IN THIS CHAPTER
Calculating sums and averages
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
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.
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 ...