Aggregation — COUNT, SUM, AVG, and GROUP BY

"How many orders per customer" isn't a row-by-row question — it's a question about groups of rows, and GROUP BY plus an aggregate function is the specific tool built for exactly that shape.

Beginner

4 min read

Aggregate functions: collapsing many rows into one number

SELECT COUNT(*) FROM orders;          -- how many orders exist, total
SELECT SUM(amount) FROM orders;        -- total revenue across all orders
SELECT AVG(amount) FROM orders;        -- average order amount
SELECT MAX(amount) FROM orders;        -- the single largest order
SELECT MIN(amount) FROM orders;        -- the single smallest order

An aggregate function takes many rows and collapses them into a single value — COUNT(*) counts rows, SUM/AVG/MAX/MIN compute over a specific numeric column. Without any GROUP BY, an aggregate function applies to the entire result set at once: SELECT SUM(amount) FROM orders returns exactly one row, one number, the total across every order in the table.

GROUP BY: applying an aggregate per group, not to everything at once

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

customer_id | order_count | total_spent
------------+-------------+------------
1           | 3           | 150
2           | 1           | 75
3           | 5           | 320

GROUP BY customer_id splits the rows into buckets — one bucket per distinct customer_id — and the aggregate functions (COUNT, SUM) run separately within each bucket, producing one result row per group instead of one row for the whole table. This is the actual mechanism behind "how many orders per customer" or "total spent per customer": each customer's orders get grouped together, and the aggregate answers the question once per group.

The rule that trips almost everyone up at first: what can appear in SELECT

-- This is an ERROR in standard SQL (and most real databases enforce it):
SELECT customer_id, product, SUM(amount) FROM orders GROUP BY customer_id;
-- product isn't in GROUP BY and isn't aggregated — which specific product's
-- name would this even show, out of potentially several per customer?

Once GROUP BY is in play, every column in SELECT has to be either listed in GROUP BY itself, or wrapped in an aggregate function — there's no third option. The reasoning is concrete: within one customer_id group there might be five different product values across five different orders, so SELECT product has no single well-defined answer for that group — SQL can't guess which one you meant, so it refuses to run the query at all rather than picking one arbitrarily.

HAVING: filtering groups, not individual rows

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

customer_id | order_count
------------+------------
3           | 5

WHERE (from the first lesson) filters individual rows before grouping happens. HAVING filters groups, after aggregation — this is exactly why HAVING can reference an aggregate like COUNT(*) > 3 while WHERE structurally can't: at the point WHERE runs, no grouping or aggregation has happened yet, so there's no aggregate value to check against. "Customers with more than 3 orders" is a question about groups, which is exactly what makes it a HAVING question, not a WHERE one.

The clause order, and why it matters

SELECT customer_id, COUNT(*) AS order_count      -- 5. which columns
FROM orders                                      -- 1. which table
WHERE amount > 0                                 -- 2. filter rows, before grouping
GROUP BY customer_id                             -- 3. group the remaining rows
HAVING COUNT(*) > 3                              -- 4. filter groups, after aggregating
ORDER BY order_count DESC;                       -- 6. sort the final result

This is the full, real order these clauses are written in — and the diagram shows the order they conceptually run in: filter rows first (WHERE), then group what's left (GROUP BY), then filter those groups (HAVING), then sort (ORDER BY). Understanding this order is what makes it obvious why WHERE can't see aggregates (they don't exist yet at that point) and why HAVING can (grouping already happened by the time it runs).

Where this shows up in Django

from django.db.models import Count
Customer.objects.annotate(order_count=Count("orders")).filter(order_count__gt=3)

Django's annotate() with an aggregate function (Count, Sum, Avg) generates exactly this GROUP BY + aggregate SQL underneath — .filter(order_count__gt=3) after an annotate() compiles to a HAVING clause specifically because it's filtering on the aggregated value, not a raw column, the same distinction this lesson's WHERE vs. HAVING section covers directly in SQL.