SQL Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single scalar value. Aggregate functions are often used with the GROUP BY clause and the HAVING clause of SQL statements.

Common Aggregate Functions

`AVG(expression)`

Returns the average value of a column.

`COUNT(expression | *)`

Returns the number of rows or non-null values in a column.

`MAX(expression)`

Returns the largest value in a set of values.

`MIN(expression)`

Returns the smallest value in a set of values.

`SUM(expression)`

Returns the sum of values in a column.

`STDEV(expression)`

Returns the standard deviation of a column.

`VAR(expression)`

Returns the variance of a column.

Syntax and Usage

Aggregate functions operate on a set of rows. When used without a GROUP BY clause, they operate on all rows selected by the query. When used with a GROUP BY clause, they operate on each group separately.

Example: Calculating Average Salary by Department

Consider a table named Employees with columns Department and Salary.


SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

Example: Counting Employees in Each Department


SELECT Department, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

More Aggregate Functions

SQL Server also provides more specialized aggregate functions:

CHECKSUM_AGG()
GROUPING()
LISTAGG() (available in newer versions or via compatibility levels)
PERCENTILE_CONT()
PERCENTILE_DISC()
STRING_AGG() (available in newer versions or via compatibility levels)

Key Concepts

NULL values: Most aggregate functions ignore NULL values in the column they are operating on, except for COUNT(*).
DISTINCT keyword: You can use the DISTINCT keyword within an aggregate function to consider only unique values. For example, COUNT(DISTINCT column_name).
GROUP BY: Groups rows that have the same values in specified columns into a summary row.
HAVING: Filters groups based on a specified condition, typically involving aggregate functions.

For detailed information on each aggregate function, including specific syntax and examples, please refer to the official SQL Server Transact-SQL reference.