Aggregation, grouping and subqueries

Why this matters

Most business questions are summaries: total sales, average order value, customers per region, the best-selling product each month. SQL answers them with aggregate functions and grouping, and extends them with subqueries and window functions for questions that need two steps. These are also where subtle errors live, because aggregates treat missing values in ways that are easy to forget.

The exam asks you to choose the right aggregation for a question and to read what a grouped query returns. This lesson covers aggregate functions and NULL, grouping and filtering groups, subqueries and common table expressions, window functions, and saving intermediate results as temporary tables and views.

The lesson

COUNT, SUM, AVG, MIN and MAX, and how each treats NULL

An aggregate function turns many rows into one value.

• COUNT(*) counts rows.
• COUNT(column) counts rows where that column is not NULL.
• COUNT(DISTINCT column) counts distinct non-NULL values.
• SUM, AVG, MIN and MAX do what their names say, over non-NULL values.

The NULL behaviour is the part to remember. Aggregates other than COUNT(*) ignore NULLs. On a table of 100 customers where 20 have no email, COUNT(*) returns 100 and COUNT(email) returns 80. AVG divides by the number of non-NULL values, not the number of rows, so the average of a column with missing values is the average of the known values only. That is often what you want, and it is not the same as treating missing values as zero: if missing means zero, it must be converted explicitly, for example with COALESCE(column, 0), which returns the first non-NULL value in its list.

Two more cautions. SUM of an empty set returns NULL, not zero. And integer division in some databases discards the fraction, so dividing two integers can return 0 where 0.4 was expected; multiplying by 1.0 or casting to a decimal type avoids it.

GROUP BY and HAVING, and the difference from WHERE

GROUP BY splits rows into groups and applies aggregates to each group:

SELECT p.category, SUM(o.quantity) AS units
FROM orders AS o
JOIN products AS p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY units DESC;

The rule is that every column in the SELECT list must either be in the GROUP BY or be inside an aggregate. A query that selects a column not in either is asking for "the" value of something that has many values per group; most databases reject it, and some return an arbitrary one, which is worse.

There are two places to filter, and they apply at different stages:

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

"Categories with more than 500 units sold, counting only 2025 orders" needs both: WHERE for the date condition on each order, and HAVING SUM(quantity) > 500 for the condition on each group's total. Putting an aggregate condition in WHERE is an error, because at that stage the groups do not yet exist.

The full logical order of a query is therefore: FROM and joins, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and finally LIMIT.

Subqueries and common table expressions

A subquery is a query inside another query. It lets one question use the answer to another.

A subquery can supply a single value: orders whose quantity is above the average quantity use WHERE quantity > (SELECT AVG(quantity) FROM orders). It can supply a list: WHERE customer_id IN (SELECT customer_id FROM customers WHERE segment = 'Business'). Or it can act as a derived table in the FROM clause, summarised first and then queried.

Nested subqueries become hard to read quickly. A common table expression (CTE), introduced with WITH, names a subquery and places it before the main query, so a multi-step analysis reads top to bottom:

WITH customer_totals AS (
    SELECT customer_id, SUM(quantity) AS units
    FROM orders
    GROUP BY customer_id
)
SELECT AVG(units) AS average_units_per_customer
FROM customer_totals;

This answers a question that a single AVG cannot: the average per customer, rather than the average per order. Keeping those two levels apart is one of the most important habits in aggregation.

Window functions: running totals, ranks and moving averages

A window function calculates across a set of rows related to the current row, without collapsing them into one row the way GROUP BY does. It is written with OVER, which defines the window.

• ROW_NUMBER(), RANK() and DENSE_RANK() number or rank rows, for example products by sales within each category, using PARTITION BY category ORDER BY units DESC. RANK leaves gaps after ties; DENSE_RANK does not.
• SUM(...) OVER (ORDER BY order_date) gives a running total.
• AVG(...) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives a seven-day moving average.
• LAG and LEAD return the value from a previous or next row, which makes period-over-period change a single expression.

PARTITION BY restarts the calculation for each group, and ORDER BY inside OVER defines the sequence. Window functions answer "top N per group" and "change since last month" questions that are awkward any other way.

Temporary tables and views

Complex analyses often build results in steps, and SQL offers two ways to keep a step's result.

A temporary table stores a result for the rest of a session and disappears when the session ends. It is useful for breaking a long analysis into stages, checking each stage's row count, and reusing an expensive intermediate result several times without recalculating it.

A view is a saved query that behaves like a table. It stores no data of its own: each time it is queried, the underlying query runs against current data. A view gives a stable, well-named interface to a complex query, such as a monthly_sales view that everyone uses, and can restrict access by exposing only certain columns or rows. Some databases also offer materialised views, which do store their result and must be refreshed, trading freshness for speed.

The choice comes down to lifetime and freshness: a temporary table for your own working steps, a view for a definition others will rely on.