Aggregate Functions

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

AVG

Returns the average value of an expression.

AVG ( [ ALL | DISTINCT ] expression ) [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]

Parameters

  • expression: Expression that aggregates values.
  • ALL: Applies the aggregate function to all values.
  • DISTINCT: Specifies that AVG returns the average of only the unique values.

Return Value

Returns the average value of expression. If the set contains no rows, AVG returns NULL. If the set contains rows with NULL values, AVG ignores them.

Examples

Find the average salary of employees in department 10:

SELECT AVG(Salary) FROM Employees WHERE DepartmentID = 10;
COUNT

Returns the number of items in a group.

COUNT ( { [ ALL ] expression | * | DISTINCT expression } ) [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]

Parameters

  • expression: An expression that is used for counting.
  • *: Specifies that COUNT returns the total number of rows in the specified table.
  • DISTINCT expression: Specifies that COUNT returns the number of unique non-NULL values of the expression.

Return Value

Returns the number of items. The data type is int.

Examples

Count the total number of employees:

SELECT COUNT(*) FROM Employees;

Count the number of distinct job titles:

SELECT COUNT(DISTINCT JobTitle) FROM Employees;
MAX

Returns the maximum value in a set of values.

MAX ( expression ) [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]

Parameters

  • expression: An expression that aggregates values.

Return Value

Returns the maximum value of expression. Ignores NULL values.

Examples

Find the highest salary:

SELECT MAX(Salary) FROM Employees;
MIN

Returns the minimum value in a set of values.

MIN ( expression ) [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]

Parameters

  • expression: An expression that aggregates values.

Return Value

Returns the minimum value of expression. Ignores NULL values.

Examples

Find the lowest salary:

SELECT MIN(Salary) FROM Employees;
SUM

Returns the sum of the values of an expression.

SUM ( [ ALL | DISTINCT ] expression ) [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]

Parameters

  • expression: Expression that aggregates values.
  • ALL: Applies the aggregate function to all values.
  • DISTINCT: Specifies that SUM returns the sum of only the unique values.

Return Value

Returns the sum of expression. If the set contains no rows, SUM returns NULL. If the set contains rows with NULL values, SUM ignores them.

Examples

Calculate the total payroll for the company:

SELECT SUM(Salary) FROM Employees;

Calculate the total salary for department 5:

SELECT SUM(Salary) FROM Employees WHERE DepartmentID = 5;

Other Aggregate Functions

SQL Server also supports other aggregate functions such as:

  • CHECKSUM_AGG
  • COUNT_BIG
  • GROUPING
  • GROUPING_ID
  • STDEV
  • STDEVP
  • VAR
  • VARP

For detailed information on these functions and more advanced usage, please refer to the official SQL Server Transact-SQL Reference.