Define Aggregate Queries during SQL Data Selection

An aggregate query^[1] is one that contains a specific Transact-SQL function that performs a calculation on a set of values. This calculation is returned by the query as a single value. For example, if you wanted to know how much you are paying out in salaries, you could ask for the total of all values in the Salary column in the Employees table. Aggregates are used in conjunction with the GROUP BY clause.

There are ten aggregate functions within the Transact-SQL language. They are:

1. AVG – Returns the average of all values in a group of values
2. COUNT – Returns the number of rows in the table(s) for which the criteria applies in a group of values
3. GROUPING – Used with On-line Analytical Processing (OLAP) functionality.
4. MAX – Returns the maximum value in a group of values
5. MIN – Returns the minimum value in a group of values
6. SUM – Returns the summarization, or addition, of all values in a group of values
7. STDEV – Returns the statistical standard deviation
8. STDEVP – Returns the statistical standard deviation for a population of values
9. VAR – Returns the statistical standard variance
10. VARP – Returns the statistical standard variance for a population of values

There are a couple of attributes to remember about these functions. Each of the functions ignore null values, except for the COUNT function. Also, each of the functions uses the following syntax:

[1] Aggregate: A query or part of a query that performs mathematical summing on one or more columns.