Microsoft SQL Server 2012 Bible

Summary

SQL Server excels in aggregate functions, with the proverbial rich suite of features, and it is capable of calculating sums and aggregates to suit nearly any need. From the simple COUNT() aggregate function to the complex dynamic crosstab query and the new PIVOT command, these query methods enable you to create powerful data analysis queries for impressive reports. The most important points to remember about aggregation are as follows:

• Aggregate queries generate a single summary row, so every column must be an aggregate function.
• No performance difference exists between COUNT(*) and COUNT(pk).
• Aggregate functions, such as COUNT(column) and AVG(column), ignore nulls, which can be a good thing, and a reason why nulls make life easier for the database developer.
• GROUP BY queries divide the data source into several segmented data sets and then generate a summary row for each group. For GROUP BY queries, the GROUP BY columns can and should be in the column list.
• In the logical flow of the query, the GROUP BY occurs after the FROM clause and the WHERE clause, so when coding the query, get the data properly selected and then add the GROUP BY.
• Complex aggregations (for example, nested aggregations) often require CTEs or subqueries. Design the query from the inside out — that is, design the aggregate subquery first and then add the outer query.
• The OVER() clause generates the sort order for the ranking functions.
• The new T-SQL functions extend the existing capabilities of the Windowing ...