To summarize all the data in a table, you create an aggregate query that involves a function such as SUM( ) or AVG( ). When you run the query, the result set contains a single row with the summary information. For example, you can calculate the total price of all books in the titles table by creating a query that sums the contents of the price column. The resulting query output might look like this:
The corresponding SQL statement might look like this:
SELECT SUM(price) total_price
FROM titles
When you use an aggregate function, by default the summary information includes all specified rows. In some instances, a result set includes non-unique rows. You can filter out non-unique rows by using the DISTINCT option of an aggregate function.
You can combine aggregate functions with other expressions to calculate other summary values.
For more information on aggregate functions, see the Microsoft SQL Server documentation.
Summary of aggregate functions
You can use the following aggregate functions:
| Aggregate function | Description |
|---|---|
| AVG(expression) | Average of the values in a column. The column can contain only numeric data. Null values are ignored. |
| BINARY_CHECKSUM(expression list) BINARY_CHECKSUM(*) |
The binary checksum value computed over a row of a table or over a list of expressions. BINARY CHECKSUM is used to detect changes to a row of a table. |
| CHECKSUM(expression) | The checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is used for building hash indices. |
| CHECKSUM_AGG(expression) | The checksum value of a group. Null values are ignored. |
| COUNT(expression) COUNT(*) COUNT_BIG(expression), COUNT_BIG(*) |
A count of the values in a column (if you specify a column name as expr) or of all rows in a table or group (if you specify *). COUNT(expr) and COUNT_BIG(expr) ignore null values, but COUNT(*) and COUNT_BIG(*) includes them in the count. COUNT always returns an int data type; COUNT_BIG always returns a bigint data type. |
| GROUPING(column_name) | An aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP. |
| MAX(expression) | Highest value in a column (last value alphabetically for text data types). Ignores null values. |
| MIN(expression) | Lowest value in a column (first value alphabetically for text data types). Ignores null values. |
| STDEV(expression) STDEVP(expression) |
The statistical standard deviation of all values in the given expression. STDEVP returns the statistical standard deviation of the population of all values. |
| SUM(expression) | Total of values in a column. The column can contain only numeric data. |
| VAR(expression) VARP(expression) |
The statistical variance of all values in the given expression. VARP returns the statistical variance of the population of all values. |