Aggregate Functions

Prerequisites: NULL: missing values

Aggregate functions combine many rows into a single value: how many, how much in total, what's the average.

Example

SELECT
  COUNT(*)             AS products,
  MIN(price)           AS cheapest,
  MAX(price)           AS most_expensive,
  ROUND(AVG(price), 2) AS average,
  SUM(price)           AS total
FROM products;

Expected output

+----------+----------+----------------+---------+--------+
| products | cheapest | most_expensive | average | total  |
+----------+----------+----------------+---------+--------+
|        6 |     35.5 |         249.99 |  112.56 | 675.39 |
+----------+----------+----------------+---------+--------+

Function	Returns
`COUNT(*)`	number of rows
`COUNT(col)`	number of non-NULL values
`SUM(col)`	total
`AVG(col)`	average
`MIN(col)`, `MAX(col)`	smallest, largest (also work on text and dates)

COUNT(*) vs COUNT(column)

SELECT COUNT(*) AS all_customers, COUNT(city) AS with_city
FROM customers;

+---------------+-----------+
| all_customers | with_city |
+---------------+-----------+
|             6 |         5 |
+---------------+-----------+

COUNT(city) skips the NULL. All aggregates except COUNT(*) ignore NULL values — so AVG averages only the known values.

Aggregates with WHERE

WHERE filters rows before they're aggregated:

SELECT SUM(quantity * unit_price) AS revenue
FROM order_items
WHERE order_id IN (1, 2);

+---------+
| revenue |
+---------+
|  409.99 |
+---------+

Common mistakes

Mixing aggregated and non-aggregated columns without GROUP BY: SELECT category, AVG(price) FROM products — PostgreSQL rejects it ("column must appear in the GROUP BY clause"), while SQLite and some MySQL settings return an arbitrary category. The next lesson shows the right way.

Practice

How many orders are there, and what's the date of the first and the most recent order?

Show solution

SELECT COUNT(*) AS orders, MIN(ordered_on) AS first, MAX(ordered_on) AS latest
FROM orders;

+--------+------------+------------+
| orders |   first    |   latest   |
+--------+------------+------------+
|      8 | 2025-01-05 | 2025-04-22 |
+--------+------------+------------+