MSDN Documentation

SQL Language Reference - Functions - Aggregate Functions

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause to return a single value for each group in the result set.

Common Aggregate Functions

COUNT()

Returns the number of rows that match a specified criterion.

Example:

SELECT COUNT(*)
FROM Employees
WHERE Department = 'Sales';

This query returns the total number of employees in the 'Sales' department.

SUM()

Returns the total sum of a numeric column.

Example:

SELECT SUM(Salary) AS TotalSalary
FROM Employees;

This query returns the sum of all salaries in the 'Employees' table.

AVG()

Returns the average value of a numeric column.

Example:

SELECT AVG(Price) AS AveragePrice
FROM Products;

This query returns the average price of all products.

MIN()

Returns the smallest value in a column.

Example:

SELECT MIN(OrderDate) AS EarliestOrder
FROM Orders;

This query returns the earliest order date.

MAX()

Returns the largest value in a column.

Example:

SELECT MAX(Score) AS HighestScore
FROM Exams;

This query returns the highest score achieved in the 'Exams' table.

Using Aggregate Functions with GROUP BY

Aggregate functions are frequently used with the GROUP BY clause to perform calculations on subsets of rows. The GROUP BY clause groups rows that have the same values in one or more columns into a summary row.

Example: Number of employees per department

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

This query returns the count of employees for each unique department.

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but WHERE filters individual rows before aggregation, while HAVING filters groups after aggregation.

Example: Departments with more than 10 employees

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

This query returns only those departments that have more than 10 employees.

Other Aggregate Functions