Aggregate functions compute a single result from a set of input values. They are commonly used with GROUP BY to produce summary data.
Common Aggregate Functions
| Function | Purpose | Syntax |
|---|---|---|
COUNT() | Returns the number of rows. | COUNT([ALL|DISTINCT] expression) |
SUM() | Adds up numeric values. | SUM([ALL|DISTINCT] expression) |
AVG() | Average of numeric values. | AVG([ALL|DISTINCT] expression) |
MIN() | Smallest value. | MIN(expression) |
MAX() | Largest value. | MAX(expression) |
STDEV() | Standard deviation of a population. | STDEV([ALL|DISTINCT] expression) |
VAR() | Variance of a population. | VAR([ALL|DISTINCT] expression) |
Examples
1. Count Orders per Customer
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
ORDER BY OrderCount DESC;
2. Average Salary by Department
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
WHERE Salary > 0
GROUP BY Department;
3. Total Sales with NULL Handling
SELECT SUM(ISNULL(Amount,0)) AS TotalSales
FROM Sales;
Using DISTINCT
You can apply DISTINCT inside an aggregate to ignore duplicate values:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Orders;