Aggregate Functions

So far every query has returned rows from the table. Aggregate functions do something different: they summarise many rows into one value — how many orders, total revenue, average salary, cheapest product. They're the foundation of every report and dashboard.

The five core aggregates

SELECT COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       round(AVG(price), 2) AS average_price,
       SUM(stock) AS units_in_stock
FROM products;

The whole table collapses into a single row of results.

Aggregates with WHERE

SELECT COUNT(*) AS developers, AVG(salary) AS average_salary
FROM employees
WHERE department_id = 2;

WHERE filters rows before they're aggregated, so this summarises only the Engineering department.

COUNT variations

SELECT COUNT(*) AS all_customers,
       COUNT(city) AS have_city,
       COUNT(DISTINCT country) AS countries
FROM customers;

COUNT(*) counts rows, COUNT(column) skips NULLs, and COUNT(DISTINCT column) counts different values.

Aggregating expressions

SELECT round(SUM(quantity * unit_price), 2) AS revenue,
       SUM(quantity) AS units_sold
FROM order_items;

Aggregates accept any expression, so you can total line values directly.

MIN and MAX work on text and dates

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order,
       MIN(status) AS first_status_alphabetically
FROM orders;

NULLs and empty sets

SELECT COUNT(*) AS rows_found, SUM(price) AS total, AVG(price) AS average
FROM products
WHERE category = 'Garden';

With no matching rows, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. Wrap them in COALESCE(SUM(price), 0) when a report needs a zero.

Aggregates also ignore NULL values, which affects averages: the average of 10, NULL and 20 is 15, not 10.

Integer averages

SELECT AVG(quantity) AS avg_quantity FROM order_items;

SQLite, MySQL, PostgreSQL and Oracle return a decimal average for integer columns, but SQL Server returns an integer (the average of 1 and 2 is 1). AVG(quantity * 1.0) avoids the surprise.

Aggregating text: string_agg

SELECT string_agg(name, ', ') AS book_titles
FROM products
WHERE category = 'Books';

Joins values into one string. It's string_agg in PostgreSQL, SQL Server and SQLite (also group_concat), GROUP_CONCAT in MySQL and LISTAGG in Oracle.

You can't mix aggregates and plain columns

SELECT name, MAX(price) FROM products;

Which name should appear next to one maximum? Standard SQL rejects this query. SQLite happens to return the name from the row with the maximum (a documented special case), and MySQL may return an arbitrary row — neither is portable. To find the product with the highest price, use ORDER BY price DESC LIMIT 1 or a subquery (lesson 29). To get a value per group, use GROUP BY — next lesson.

Try it yourself

How many orders have been delivered, and what was the date range of all orders?