Skip to main content

Chapter 4: Aggregation

Every query so far returns individual rows. Most business questions don't work that way. "How many products do we carry?" "What is our average order value?" "Which category sells the most?" These questions summarize across many rows. That is aggregation.

4.1 Aggregate Functions​

An aggregate function collapses many rows into a single result.

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
FunctionReturns
COUNT(*)Total rows
COUNT(column)Non-NULL values in that column
SUM(column)Total of all values
AVG(column)Average of all values
MIN(column)Smallest value
MAX(column)Largest value

COUNT(*) and COUNT(column) are not the same. COUNT(*) counts every row. COUNT(column) skips NULLs.

SELECT
    COUNT(*)            AS total_orders,
    COUNT(shipped_date) AS shipped_orders
FROM sales.orders;
total_orders | shipped_orders
-------------+----------------
1615         | 1598

The 17-order gap is orders still in transit. Every aggregate function except COUNT(*) ignores NULL values.

4.2 GROUP BY​

GROUP BY collapses rows into one result per group instead of one result overall.

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

Every column in SELECT must either appear in GROUP BY or be inside an aggregate function. Selecting a plain column that is neither will return an error in standard SQL.

Interview Angle "Why can't you select a non-aggregated column that is not in GROUP BY?" Because aggregation only produces a single value per group for columns that are either the grouping key or the result of an aggregate. Any other column has no defined single value to return.

4.3 HAVING​

WHERE filters rows before grouping. HAVING filters groups after aggregation.

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

Use WHERE to filter individual rows. Use HAVING to filter the aggregated results.

Execution order:
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

An aggregate function cannot appear in WHERE because grouping has not happened yet at that point. HAVING is the only clause that can reference aggregate results.

Interview Angle "What is the difference between WHERE and HAVING?" WHERE filters rows before grouping. HAVING filters groups after aggregation. That is also why aggregate functions cannot appear in WHERE but can appear in HAVING.

4.4 COUNT(DISTINCT ...)​

When you join a one-to-many relationship, row counts inflate. COUNT(DISTINCT column) counts unique values instead of rows.

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

Without DISTINCT, the result would count every line item across all orders, not every order.

4.5 GROUP BY Multiple Columns​

Each unique combination of grouped columns 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
6            | 9        | 41
6            | 5        | 30

4.6 Aggregating Across Joins​

Joins assemble data from multiple tables. Aggregation summarizes it. Together they answer questions that neither can answer alone.

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

4.7 Putting It Together​

Top 5 product categories by total revenue, only including categories with at least 50 completed orders.

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

Summary​

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one value. All except COUNT(*) ignore NULL values. GROUP BY groups rows so aggregation produces one result per group. Every selected column must be in GROUP BY or wrapped in an aggregate. HAVING filters groups after aggregation. COUNT(DISTINCT column) counts unique values and corrects the inflation caused by one-to-many joins. Multiple columns in GROUP BY produce one group per unique combination.

Exercises​

4.1 - Write a query that returns the total number of customers and the number with a non-NULL phone value.

4.2 - Group production.products by brand_id and return the product count and average list_price per brand, sorted by average price descending.

4.3 - Using HAVING, find every brand with an average list_price above $1000.

4.4 - The following query does not 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;

4.5 - Write a query that joins sales.orders, sales.order_items, and production.products, then returns total quantity sold per product name, sorted from highest to lowest, limited to the top 10.

4.6 - Think About It: A colleague joins orders to order_items and reports "we received 4,658 orders last year." Explain why this count is likely wrong and write the corrected version.