Chapter 7: Aggregation
Every query so far has answered questions about individual rows: which products, which customers, which orders. Most business questions don't work that way. "How many products do we carry?" "What's our average order value?" "Which category sells the most?" None of these point at a single row; they summarize across many rows at once. That summarizing is aggregation, and it's the most direct path from raw data to something a manager can act on.
7.1 The Aggregate Functions
An aggregate function takes many rows and collapses them into one number.
SELECT COUNT(*) AS total_products
FROM production.products;
total_products
--------------
321
SELECT
AVG(list_price) AS avg_price,
MIN(list_price) AS cheapest,
MAX(list_price) AS priciest,
SUM(list_price) AS catalog_value
FROM production.products;
avg_price | cheapest | priciest | catalog_value
----------+----------+----------+---------------
1410.45 | 89.99 | 11999.99 | 452755.45
| Function | Returns |
|---|---|
COUNT(*) | Number of rows |
COUNT(column) | Number of non-NULL values in that column |
SUM(column) | Total of all values |
AVG(column) | Average of all values |
MIN(column) / MAX(column) | Smallest / largest value |
COUNT(*) and COUNT(column) are not the same thing.
SELECT
COUNT(*) AS total_orders,
COUNT(shipped_date) AS shipped_orders
FROM sales.orders;
total_orders | shipped_orders
-------------+----------------
1615 | 1598
COUNT(*) counts every row regardless of content. COUNT(shipped_date) only counts rows where shipped_date is not NULL; the 17-order gap is exactly the number of orders still in transit. Every aggregate function except COUNT(*) quietly ignores NULL values; AVG divides by the count of non-NULL rows, not the total row count.
7.2 Grouping Rows with GROUP BY
A single aggregate collapses the whole table into one number. GROUP BY collapses the table into one number per group.
SELECT
category_id,
COUNT(*) AS product_count,
AVG(list_price) AS avg_price
FROM production.products
GROUP BY category_id
ORDER BY product_count DESC;
category_id | product_count | avg_price
-------------+----------------+-----------
6 | 98 | 1825.30
7 | 74 | 1340.10
1 | 59 | 245.60
3 | 41 | 510.25
2 | 28 | 380.90
4 | 12 | 1675.00
5 | 9 | 2890.45
Every row is sorted into a bucket by category_id first, then COUNT(*) and AVG(list_price) are calculated separately within each bucket. The rule that makes this work: every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. category_id satisfies the first condition; product_count and avg_price satisfy the second. Selecting a plain column that's neither grouped nor aggregated — product_name here, for instance — is undefined, since 98 different product names exist for category 6 alone, and SQL has no rule for picking just one.
Interview Angle "Why can't you select a non-aggregated column alongside
GROUP BYwithout including it in the grouping?" Some databases (MySQL in certain modes) allow it and silently pick an arbitrary row; standard SQL rejects it outright. The correct answer is that aggregation only has a well-defined value per group for columns that are either the grouping key or the result of an aggregate function; anything else has no single correct value to return.
7.3 Filtering Groups with HAVING
WHERE filters individual rows before grouping happens. HAVING filters entire groups after the aggregation is computed. They look similar and solve completely different problems.
SELECT
category_id,
COUNT(*) AS product_count
FROM production.products
GROUP BY category_id
HAVING COUNT(*) > 50;
category_id | product_count
-------------+----------------
6 | 98
7 | 74
WHERE category_id > 50 would be comparing a category ID to 50, which is nonsense. HAVING COUNT(*) > 50 is comparing the size of each group to 50, which is the actual question being asked: which categories have more than 50 products. The two clauses work together when a query needs both:
SELECT
category_id,
COUNT(*) AS premium_count
FROM production.products
WHERE list_price > 1000
GROUP BY category_id
HAVING COUNT(*) >= 5;
WHERE first throws out every product priced $1000 or under. Then the remaining rows are grouped by category. Then HAVING keeps only the categories where at least 5 expensive products survived. This completes the execution order that Chapter 4 left unfinished:
SQL Logical Order of Execution
1. FROM ─▶ identify the table(s) involved
2. JOIN ─▶ combine rows from multiple tables
3. WHERE ─▶ filter individual rows
4. GROUP BY ─▶ collapse rows into groups
5. HAVING ─▶ filter entire groups
6. SELECT ─▶ pick / compute the final columns
7. ORDER BY ─▶ sort the final result
8. OFFSET/FETCH─▶ limit how many rows return
Written: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY
Executed: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → OFFSET/FETCH
Every clause only has access to whatever the step before it produced. WHERE (step 3) runs before grouping exists, so it only ever sees raw, ungrouped rows; there's no COUNT or SUM for it to reference yet. GROUP BY (step 4) then collapses those rows into groups, which is exactly what makes HAVING (step 5) able to filter on aggregate results that didn't exist a step earlier. SELECT (step 6) runs after all of that, which is also the reason, going back to Chapter 4, that a column alias defined in SELECT can't be reused in WHERE: at the time WHERE runs, SELECT hasn't executed yet.
WHERE only ever sees raw rows; it runs before grouping exists, which is exactly why an aggregate function can never appear inside a WHERE clause. HAVING is the only clause that's allowed to filter on the result of COUNT, SUM, AVG, and the rest.
Interview Angle "What's the difference between
WHEREandHAVING?" The accurate answer isn't just "WHEREis for rows,HAVINGis for groups"; it's explaining why:WHEREexecutes beforeGROUP BY, when no aggregate values exist yet, so it physically cannot referenceCOUNT()orSUM().HAVINGexecutes after, once those values exist.
7.4 COUNT(DISTINCT ...)
Chapter 5 ended with a join that quietly multiplied every order row by however many line items it had, producing a COUNT(*) of 4,658 against a database that doesn't have anywhere near that many orders. Aggregation is the fix that was missing at the time.
SELECT COUNT(DISTINCT o.order_id) AS total_orders
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi
ON o.order_id = oi.order_id;
total_orders
------------
1615
1615, matching the real order count from section 7.1, not the inflated line-item count from before. COUNT(DISTINCT column) counts unique values rather than rows, which undoes exactly the duplication a one-to-many join introduces. The lesson from Chapter 5 closes here: a join's row count and the true count of whatever entity you care about are not the same thing unless you make them the same thing on purpose.
7.5 Grouping by Multiple Columns
GROUP BY accepts more than one column; each unique combination becomes its own group.
SELECT
category_id,
brand_id,
COUNT(*) AS product_count
FROM production.products
GROUP BY category_id, brand_id
ORDER BY category_id, product_count DESC;
category_id | brand_id | product_count
-------------+----------+----------------
1 | 9 | 22
1 | 8 | 19
1 | 3 | 18
6 | 9 | 41
6 | 5 | 30
Category 1 and brand 9 together form one group; category 1 and brand 8 form a different one, even though they share the same category. The output has one row per distinct (category_id, brand_id) pair that actually occurs in the data; combinations that don't exist in any product simply don't appear.
7.6 Aggregating Across Joins
Joins assemble the data; aggregation summarizes it. Together they answer the kind of question a join alone never could; total spend per customer requires combining three tables and then collapsing each customer's many order lines into one number.
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS total_spent
FROM sales.customers AS c
INNER JOIN sales.orders AS o
ON c.customer_id = o.customer_id
INNER JOIN sales.order_items AS oi
ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;
customer_id | first_name | last_name | order_count | total_spent
-------------+------------+-----------+--------------+-------------
142 | Maria | Lopez | 4 | 18420.55
89 | Daniel | Cho | 3 | 15990.20
207 | Priya | Nair | 5 | 14225.80
COUNT(DISTINCT o.order_id) is doing the same protective work as section 7.4; without it, a customer with several multi-item orders would inflate their own order count the same way the whole-database query did earlier. SUM then totals the actual revenue across every line item belonging to that customer, discount included.
7.7 Putting It Together
Leadership wants the top 5 product categories by total revenue, but only categories that have generated at least 50 orders, so a single lucky high-ticket sale in an otherwise quiet category doesn't skew the ranking.
SELECT
cat.category_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.quantity * oi.list_price * (1 - oi.discount)) AS total_revenue
FROM production.categories AS cat
INNER JOIN production.products AS p
ON cat.category_id = p.category_id
INNER JOIN sales.order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN sales.orders AS o
ON oi.order_id = o.order_id
GROUP BY cat.category_name
HAVING COUNT(DISTINCT o.order_id) >= 50
ORDER BY total_revenue DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
category_name | order_count | total_revenue
-------------------+--------------+----------------
Mountain Bikes | 612 | 892450.30
Road Bikes | 540 | 745210.15
Cruisers Bicycles | 215 | 198630.40
Four joins to assemble the full chain from category down to order, GROUP BY to collapse it per category, HAVING to enforce the 50-order minimum, COUNT(DISTINCT ...) to keep the order count honest, and ORDER BY with pagination to return just the top 5. Every tool from Chapters 3 through 6 working together on one real question.
Summary
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one number; all of them except COUNT(*) ignore NULL values. GROUP BY collapses rows into one number per group instead of one number overall, and every selected column must either be in the GROUP BY list or wrapped in an aggregate. HAVING filters groups after aggregation, while WHERE filters rows before it, which is also why aggregate functions can never appear inside WHERE. COUNT(DISTINCT column) counts unique values rather than rows, which is the fix for the row-multiplication a one-to-many join introduces. GROUP BY accepts multiple columns, producing one group per unique combination. Joins and aggregation combine naturally: assemble the data across tables first, then collapse it into the summary the question actually asked for.
Exercises
7.1. Write a query that returns the total number of customers, and separately, the number of customers who have a non-NULL phone value.
7.2. Write a query that groups production.products by brand_id and returns the count of products and the average list_price for each brand, sorted by average price descending.
7.3. Using HAVING, write a query that finds every brand with an average list_price above $1000.
7.4. The following query is meant to find customers who spent more than $5,000 total, but it doesn't run. Explain the error and fix it:
SELECT customer_id, SUM(list_price) AS total
FROM sales.order_items
WHERE SUM(list_price) > 5000
GROUP BY customer_id;
7.5. Write a query that joins sales.orders, sales.order_items, and production.products, then returns the total quantity sold per product_name, sorted from highest to lowest, limited to the top 10.
7.6. A colleague writes SELECT store_id, COUNT(*) FROM sales.orders JOIN sales.order_items ON ... GROUP BY store_id to find how many orders each store has handled. Explain why this count is likely wrong and write the corrected version.
7.7. Think About It: WHERE cannot reference an aggregate function, but HAVING can reference a column that isn't aggregated, as long as it's in the GROUP BY list. Using the execution order from section 7.3, explain why each of these rules exists: what does each clause actually have access to at the moment it runs?