T-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.

Overview

Aggregate functions operate on rows within a group, or on the entire result set if no grouping is specified. They are particularly useful for summarizing data, such as calculating total sales, average prices, or counting records.

Common Aggregate Functions

AVG()

Calculates the average value of an expression.

COUNT()

Counts the number of rows or non-NULL values.

MAX()

Returns the maximum value in a set.

MIN()

Returns the minimum value in a set.

SUM()

Calculates the sum of values in an expression.

STDEV()

Calculates the statistical standard deviation.

VAR()

Calculates the statistical variance.

AVG (Transact-SQL)

Returns the average value of all the values in a specified column.

AVG( expression )

expression is an expression of the exact numeric data type category, except for the bit data type. AVG cannot be used with columns that have text or ntext data types.

Example:

Find the average salary of employees in the 'Sales' department.

SELECT AVG(Salary) AS AverageSalary
FROM Employees
WHERE Department = 'Sales';

COUNT (Transact-SQL)

Counts the number of items in a group.

COUNT( { [ ALL | DISTINCT ] expression ] | * )

DISTINCT expression: Specifies that COUNT returns the number of unique, non-NULL values of expression.
*: Specifies that COUNT returns the total number of rows in the specified table.

Example:

Count the total number of employees.

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

Count the number of employees with a salary greater than 50000.

SELECT COUNT(EmployeeID) AS HighEarners
FROM Employees
WHERE Salary > 50000;

MAX (Transact-SQL)

Returns the maximum value in a set of values.

MAX( expression )

expression can be of any data type that can be ordered.

Example:

Find the highest salary in the company.

SELECT MAX(Salary) AS HighestSalary
FROM Employees;

MIN (Transact-SQL)

Returns the minimum value in a set of values.

MIN( expression )

expression can be of any data type that can be ordered.

Example:

Find the earliest hire date.

SELECT MIN(HireDate) AS EarliestHire
FROM Employees;

SUM (Transact-SQL)

Calculates the sum of the values in a numeric column.

SUM( expression )

expression is an expression of any of the numeric data types. SUM ignores NULL values. If there are no rows to sum, SUM returns NULL.

Example:

Calculate the total payroll for the 'IT' department.

SELECT SUM(Salary) AS TotalITPayroll
FROM Employees
WHERE Department = 'IT';

STDEV (Transact-SQL)

Calculates the statistical standard deviation of all values in an expression.

STDEV( [ ALL | DISTINCT ] expression )

This function calculates the standard deviation for a sample. For a population standard deviation, use STDEVP.

Example:

Calculate the standard deviation of salaries.

SELECT STDEV(Salary) AS SalaryStandardDeviation
FROM Employees;

VAR (Transact-SQL)

Calculates the statistical variance of all values in an expression.

VAR( [ ALL | DISTINCT ] expression )

This function calculates the variance for a sample. For a population variance, use VARP.

Example:

Calculate the variance of salaries.

SELECT VAR(Salary) AS SalaryVariance
FROM Employees;