Aggregation and Grouping

Aggregate functions#

An aggregate function collapses many rows into a single summary value. The five you'll use constantly:

SELECT COUNT(*) FROM orders;                -- total number of orders
SELECT SUM(quantity) FROM orders;           -- total units ordered, across all orders
SELECT AVG(price) FROM products;            -- average product price
SELECT MIN(price), MAX(price) FROM products;

COUNT(*) counts rows regardless of content. COUNT(column) counts only rows where that column is not NULL — the two can give different answers if the column is nullable:

SELECT COUNT(*) FROM users;          -- every user row
SELECT COUNT(country) FROM users;    -- only users with a non-null country

GROUP BY#

Without GROUP BY, an aggregate collapses the entire table into one row. GROUP BY first splits rows into buckets by one or more columns, then applies the aggregate separately within each bucket:

SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category;

category      product_count   avg_price
Electronics   2               56.99

Every column in the SELECT list that isn't wrapped in an aggregate function must appear in GROUP BY — the database needs to know, for every non-aggregated column, exactly one value to show per group. This query answers "total spent per customer":

SELECT u.name, SUM(p.price * o.quantity) AS total_spent
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
GROUP BY u.name
ORDER BY total_spent DESC;

HAVING vs WHERE#

Both filter rows, but at different stages of query execution:

• WHERE filters individual rows before grouping happens. It can't reference an aggregate function, because aggregates don't exist yet at that point.
• HAVING filters groups, after GROUP BY has produced them, and can reference aggregate functions freely.

-- WHERE: filter rows before grouping (only shipped orders count toward the total)
SELECT u.name, SUM(o.quantity) AS units
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
GROUP BY u.name;

-- HAVING: filter groups after grouping (only customers who ordered more than 2 units, total)
SELECT u.name, SUM(o.quantity) AS units
FROM orders o
INNER JOIN users u ON o.user_id = u.id
GROUP BY u.name
HAVING SUM(o.quantity) > 2;

Both can appear in the same query, and often should — WHERE narrows down the raw rows cheaply before grouping, and HAVING filters the resulting aggregates:

SELECT category, AVG(price) AS avg_price
FROM products
WHERE stock > 0                 -- only in-stock products count
GROUP BY category
HAVING AVG(price) > 30;         -- only categories averaging over $30

The full clause order#

SQL clauses have a fixed order in the query text, and a related — but different — logical order of execution:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

Logically, the database evaluates FROM/joins first, then WHERE, then GROUP BY, then HAVING, then computes the SELECT list, then ORDER BY, then LIMIT. That's why WHERE can't reference a SELECT-list alias in some databases, and why HAVING can reference aggregates that don't exist until after grouping.

Common mistakes#

• Using HAVING for a condition that doesn't involve an aggregate (e.g. HAVING status = 'shipped'). It works in some databases but is doing WHERE's job late and inefficiently — filter as early as possible with WHERE.
• Selecting a non-aggregated, non-grouped column and expecting one arbitrary value per group. Standard SQL rejects this outright; some databases silently allow it and pick an unspecified row's value, which is a subtle source of nondeterministic results.
• Confusing COUNT(*) and COUNT(column) when the column can be NULL — they are not interchangeable.
• Forgetting that an average of an average is not the same as the true average — AVG over a GROUP BY result needs to be weighted correctly if you then aggregate those group averages again.