Microsoft SQL Server 2012 Bible

Aggregating Data

When a T-SQL query is written that includes an aggregate function, it returns a single row of computed summarized values or values grouped by other columns in the query. More complex aggregate queries can slice the selected rows into subsets and then summarize every subset. Types of aggregate calculations range from totaling the data to performing basic statistical operations. In the logical order of the SQL query, the aggregate functions (indicated by the Summing function in the diagram) occur following the FROM clause and the WHERE filters. This means that the data can be assembled and filtered prior to being summarized without needing to use a subquery; although, sometimes a subquery is still needed to build more complex aggregate queries.

Basic Aggregations

SQL includes a set of aggregate functions, listed in Table 10.1, which you can use as expressions in the SELECT statement to return summary data.

Table 10.1 Basic Aggregate Functions

Aggregate Function	Data Type Supported	Description
sum()	Numeric	Totals all the non-null values in the column.
avg()	Numeric	Averages all the non-null values in the column. The result has the same data type as the input, so the input is often converted to a higher precision, such as avg(cast col as float).
min()	Numeric, string, datetime	Returns the smallest number or the first datetime or the first string according to the current collation from the column.
max()	Numeric, string, datetime	Returns the largest number or ...