Implement aggregate queries
May include but is not limited to: built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING.
Aggregation is a powerful feature of SQL Server, which enables you to aggregate data. After this fantastic prologue, let’s see the built-in aggregate functions of SQL Server 2008:
- AVG: returns the average of the result set.
- CHEKSUM_AGG: returns the checksum of all values of the result set.
- COUNT: returns the number of rows in the result set. NULL values omitted.
- COUNT_BIG: same as COUNT, but returns a bigint.
- GROUPING: returns 0 for detail rows, and 1 for aggregate rows, when the GROUP BY clause is used.
- MAX: returns the maximum value of a sequence.
- MIN: returns the minimum value of a sequence.
- SUM: returns the total of a result set.
The others aren’t so interesting. When working with aggregate functions, you often want to group your data based on those aggregates. You can do so by specifying the GROUP BY clause. All columns listed in the SELECT clause must be an aggregate, or listed in the GROUP BY clause.
You can use the WITH CUBE and WITH ROLLUP functions to return subtotals for more than one column. WITH ROLLUP is used to get subtotals for a single column. WITH CUBE can handle multiple columns.
To filter the results based on an aggregate function, you can use the HAVING clause. It is very similar to the WHERE clause, the key difference is that it is designed to work with aggregates.