Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Chapter 7. Aggregation and Grouping

When creating reports or analyzing business data, viewing the individual records in a table can be like looking at a map through a microscope. It's just a little too close to get the right perspective. Looking up a single record to find out when a customer bought a specific product may be valuable when that's all you care about, but perhaps you're more interested in how many products the customer bought during a period of time, or how much he or she spent on all purchases. Business leaders most often want to understand the bigger picture still—maybe they need to know how many products various customers in different geographic regions bought over a period of time. As you need to "zoom out" to view data from a larger point of view, you will inevitably need to group individual records together and then sum up, average, or count the details. This is called aggregation.

The term "aggregation" refers to something that is a summary of something else. In this context, an aggregate function returns a single value for a group of records. You can use aggregate functions in two different ways. You can "roll up," or summarize, all the rows returned by a query. Or, you also can apply aggregation at a group level, showing summarized values for the rows having the same valuesin the columns you designate for grouping. Remember that information is meaningful; data is little more than values stored in a table. Often, the information part of the equation comes from ...