Microsoft® SQL Server® 2012 Bible

249

CHAPTER

10

Aggregating, Windowing, and

Ranking Data

IN THIS CHAPTER

Understanding Basic Aggregations

Using SQL Server Aggregation Functions

Customizing Aggregation Sets

Understanding Windowing

Ranking Functions

I

nformation is the foundation of most organizational decisions. In its raw form, that data is

meaningless and offers little value to an information wonk or data analyst; not until the data

is summarized or grouped is it considered useful information. Although summarization and

analysis can certainly be performed with other tools, such as Reporting Services, Analysis Services,

PowerPivot, or an external tool such as SAS, SQL is a set-based language, and a fair amount of sum-

marizing and grouping can be performed well within the SQL SELECT statement.

SQL excels at calculating sums, max values, and averages for the entire data set or for segments of

data. In addition, the product of SQL queries can be as simple as a table to a complex pivot table

that includes subtotals and totals. In this chapter you explore different ways to group, gather, and

summarize data within SQL Server.

Aggregating Data ...............................................................................................

When a T-SQL query is written that includes an aggregate function, it returns a single row of com-

puted summarized values or values grouped by other columns in the query. More complex aggregate

queries can slice the selected rows into subsets and then summarize every subset. Types of aggre-

gate calculations range from totaling the data to performing basic statistical operations. In the

logical order of the SQL query, the aggregate functions (indicated by the Summing function in the

c10.indd 249c10.indd 249 7/30/2012 4:26:10 PM7/30/2012 4:26:10 PM

http://www.it-ebooks.info