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 to perform calculations on each group of rows.

Common Aggregate Functions

AVG(expression): Returns the average value of a column. Ignores NULL values.
COUNT(expression | *): Returns the number of rows in a group or the number of non-NULL values in a column. COUNT(*) counts all rows.
MAX(expression): Returns the maximum value in a set of values.
MIN(expression): Returns the minimum value in a set of values.
SUM(expression): Returns the sum of all values in a numeric expression. Ignores NULL values.
GROUP_CONCAT(expression SEPARATOR sep): Concatenates all non-NULL values of an expression into a single string. You can specify a separator. (Syntax may vary slightly between SQL dialects).
STDEV(expression): Returns the statistical standard deviation of all values in the specified expression.
VAR(expression): Returns the statistical variance of all values in the specified expression.

Using Aggregate Functions with `GROUP BY`

The GROUP BY clause groups rows that have the same values in one or more columns into a summary row, like "find the total sales for each region". Aggregate functions are typically used with GROUP BY.

Example: Calculating Average Salary per Department

Suppose you have an Employees table with Department and Salary columns.


SELECT
    Department,
    AVG(Salary) AS AverageSalary
FROM
    Employees
GROUP BY
    Department;

This query will return the average salary for each unique department found in the Employees table.

Using Aggregate Functions with `HAVING`

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but HAVING operates on the results of aggregate functions.

Example: Finding Departments with Average Salary Above a Threshold


SELECT
    Department,
    AVG(Salary) AS AverageSalary
FROM
    Employees
GROUP BY
    Department
HAVING
    AVG(Salary) > 60000;

This query returns only those departments where the average salary is greater than 60,000.

Note: When using aggregate functions without a GROUP BY clause, they operate on the entire result set of the query, returning a single value. If a GROUP BY clause is present, the aggregate function operates on each group separately.

Distinct Aggregate Values

You can use the DISTINCT keyword within an aggregate function to operate on unique values only.

Example: Counting Distinct Departments


SELECT
    COUNT(DISTINCT Department) AS NumberOfDistinctDepartments
FROM
    Employees;