SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX and More

An aggregate function takes many values and returns one: a count, a total, an average. You have met each of them individually. This lesson puts them together and covers the rules that apply to all of them.

The example tables#

employees

products

The five core aggregates#

A complete summary in one query#

Use several aggregates side by side to build a summary report:

SELECT department,
       COUNT(*)              AS people,
       SUM(salary)           AS payroll,
       ROUND(AVG(salary), 0) AS avg_salary,
       MIN(salary)           AS lowest,
       MAX(salary)           AS highest
FROM employees
GROUP BY department
ORDER BY department;

department  | people | payroll | avg_salary | lowest | highest
------------+--------+---------+------------+--------+--------
Engineering | 2      | 159000  | 79500      | 78000  | 81000
Marketing   | 1      | 46000   | 46000      | 46000  | 46000
Sales       | 2      | 113000  | 56500      | 52000  | 61000

All five functions see the same groups, so the numbers are consistent with each other.

Rule 1: aggregates collapse rows#

Without GROUP BY, the whole table becomes one group and you get exactly one row. With GROUP BY, you get one row per group. Every column you select must therefore be either inside an aggregate or listed in GROUP BY.

-- Valid: department is grouped, everything else is aggregated
SELECT department, MAX(salary) FROM employees GROUP BY department;

-- Invalid in most databases: name is neither grouped nor aggregated
SELECT department, name, MAX(salary) FROM employees GROUP BY department;

Rule 2: aggregates skip NULLs#

All aggregates except COUNT(*) ignore NULL values. That makes AVG an average of known values, and COUNT(column) a count of filled-in values.

Rule 3: empty input gives NULL (except COUNT)#

When no rows reach the aggregate:

SELECT COUNT(*)    AS n,
       SUM(amount) AS total,
       AVG(amount) AS average
FROM orders
WHERE amount > 10000;

n | total | average
--+-------+--------
0 | NULL  | NULL

COUNT returns 0; the others return NULL. Wrap them in COALESCE(..., 0) if your report needs zeros.

Rule 4: aggregates cannot go in WHERE#

Use HAVING to filter on an aggregate result, or a subquery to compare rows with one. See HAVING and Subqueries.

Aggregating expressions#

The argument can be any expression, calculated per row before aggregating:

SELECT category,
       COUNT(*)   AS products,
       SUM(stock) AS units,
       MIN(price) AS min_price,
       MAX(price) AS max_price
FROM products
GROUP BY category
ORDER BY units DESC;

category    | products | units | min_price | max_price
------------+----------+-------+-----------+----------
Stationery  | 1        | 500   | 60        | 60
Electronics | 3        | 52    | 2500      | 55000
Furniture   | 2        | 13    | 4500      | 7000

DISTINCT inside aggregates#

COUNT(DISTINCT x), SUM(DISTINCT x) and AVG(DISTINCT x) consider each different value only once. COUNT(DISTINCT customer_id) is very common ("how many different customers ordered?"); the others are rarely what you want.

Joining text: string aggregation#

Most databases can also combine text from many rows into one string:

-- SQLite (GROUP_CONCAT also works in MySQL)
SELECT department,
       GROUP_CONCAT(name, ', ') AS members
FROM employees
GROUP BY department
ORDER BY department;

department  | members
------------+------------
Engineering | Ravi, Meera
Marketing   | John
Sales       | Asha, Priya

Statistical aggregates#

Many databases also provide STDDEV (standard deviation), VARIANCE, and percentile functions such as PERCENTILE_CONT for medians. Names vary; check your database's documentation.

Aggregates vs window functions#

Aggregates collapse rows: ten employees become three department rows. Sometimes you want the department total next to each employee instead. That is what window functions do: SUM(salary) OVER (PARTITION BY department).

Common mistakes#

Practice#

SELECT region,
       SUM(revenue)           AS total,
       ROUND(AVG(revenue), 1) AS avg_month,
       MAX(revenue)           AS best_month
FROM monthly_sales
GROUP BY region;

region | total | avg_month | best_month
-------+-------+-----------+-----------
North  | 550   | 137.5     | 170
South  | 435   | 108.8     | 130

Key takeaway#