SQL Aggregate Functions

SQL Server > Aggregate Functions

SQL 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 to perform calculations on groups of rows.

Common Aggregate Functions

COUNT()

Returns the number of rows that match a specified criterion.

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

COUNT(column_name) counts non-NULL values in a column. COUNT(*) counts all rows.

SUM()

Returns the total sum of a numeric column.

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

Can only be used on numeric data types.

AVG()

Returns the average value of a numeric column.

SELECT AVG(Salary)
FROM Employees
WHERE Department = 'Marketing';

Ignores NULL values in the calculation.

MIN()

Returns the minimum value in a column.

SELECT MIN(OrderDate)
FROM Orders;

Works with numeric, date, and string data types.

MAX()

Returns the maximum value in a column.

SELECT MAX(Price)
FROM Products;

Works with numeric, date, and string data types.

Using Aggregate Functions with GROUP BY

Aggregate functions are powerful when combined with the GROUP BY clause to summarize data for each group.

Example: Total Salary per Department

This query calculates the total salary for all employees within each department.

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Example: Number of Employees per Department

This query counts the number of employees in each department.

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

Using Aggregate Functions with HAVING

The HAVING clause is used to filter groups based on a specified condition, often involving aggregate functions. It's like a WHERE clause for groups.

Example: Departments with More Than 10 Employees

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

Distinct Values

The DISTINCT keyword can be used with aggregate functions to operate only on unique values.

Example: Count of Unique Job Titles

SELECT COUNT(DISTINCT JobTitle)
FROM Employees;

Advanced Aggregate Functions

GROUP_CONCAT() (MySQL/SQLite) / STRING_AGG() (SQL Server/PostgreSQL)

Concatenates values from multiple rows into a single string.

-- SQL Server / PostgreSQL
SELECT Department, STRING_AGG(EmployeeName, ', ') AS EmployeesInDepartment
FROM Employees
GROUP BY Department;

ROW_NUMBER(), RANK(), DENSE_RANK()

These are window functions, not traditional aggregate functions, but are often discussed in the same context. They assign a sequential integer rank to each row within a partition of a result set.

SELECT
    EmployeeName,
    Department,
    Salary,
    ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) as RowNum
FROM Employees;