Microsoft® SQL Server® 2008 Bible

Chapter 12. Aggregating Data

IN THIS CHAPTER

• Calculating sums and averages

• Statistical analysis

• Grouping data within a query

• Solving aggravating aggregation problems

• Generating cumulative totals

• Building crosstab queries with the case, pivot, and dynamic methods

The Information Architecture Principle in Chapter 2 implies that information, not just data, is an asset. Turning raw lists of keys and data into useful information often requires summarizing data and grouping it in meaningful ways. While summarization and analysis can certainly be performed with other tools, such as Reporting Services, Analysis Services, or an external tool such as SAS, SQL is a set-based language, and a fair amount of summarizing and grouping can be performed very 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, SQL queries can create cross-tabulations, commonly known as pivot tables.

Simple Aggregations

The premise of an aggregate query is that instead of returning all the selected rows, SQL Server returns a single row of computed values that summarizes the original data set, as illustrated in Figure 12-1. More complex aggregate queries can slice the selected rows into subsets and then summarize every subset.

The types of aggregate calculations range from totaling the data to performing basic statistical operations.

It's important to note that in the logical order of the SQL query, the aggregate functions (indicated ...