Lesson 5 of 7 · 50 min

Use CASE and show zero activity deliberately

Learn the reasoning. Try the task. Compare your answer.

Prepared for DataCareerHub · Curriculum version 2026-10-15.3

By the end of this lesson

Build separate status counts with conditional aggregation.
Create an explicit month-channel reporting grid.
Distinguish an observed zero from missing or out-of-scope data.

Use the practice filesReady-to-open SQLite database ↓14 practice starters ↓Worked SQL solutions ↓Setup & data dictionary ↓All datasets & instructions ↗

SQLite syntax · Read-only exercises run on your local practice copy · Monetary inputs are integer cents, converted to EUR in the query outputs.

Understand the method

Conditional aggregation lets several measures share one declared grouping. Instead of running a separate query for each status, evaluate a condition for each row and sum its contribution. Keep the meaning of each measure explicit. A returned-order count is not a refund value, and an order-status snapshot is not an event history.

Read CASE as a row-level rule

SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)
       AS completed_orders
FROM orders;

Each completed row contributes one; other rows contribute zero. Q10 applies three such expressions within monthly groups. Because verified rows are unique orders, the results are counts of orders in each final status. If you applied the same logic to order_items after a join, it would count matched lines unless you changed the method.

An ELSE branch makes the contribution for nonmatches clear. An omitted ELSE returns NULL. SUM ignores NULL inputs, which can be useful in some expressions but can also make the behaviour less obvious. Do not assume CASE repairs a missing field or validates the source. It implements the rule you wrote.

Why a month can disappear

A grouped query returns groups found in its input. It does not automatically produce every month/channel pair that a report expects. The partner channel has no orders, so a simple orders GROUP BY will not show it. If a stakeholder expects all channels in both reporting months, define that reporting grid separately and join the observed aggregate to it.

Q11 declares a two-row months CTE using VALUES, crosses it with the three-row channels dimension, and gets six expected combinations. CROSS JOIN is intentional here: two months times three channels. It would be dangerous to apply an unplanned cross join to large event tables. Count the expected grid rows before adding the measures.

Aggregate first, join second

The monthly_sales CTE has one row per observed month/channel with completed-order cents. LEFT JOIN it to the reporting grid on both month and channel. Omitting either key would mix months or repeat channel amounts. Present missing aggregates as zero only because the exercise assumes complete coverage of both months and a complete channel dimension. The dictionary states that assumption.

If March data had not been supplied, filling March with zero would imply a fact the evidence cannot establish. A production report would need a coverage control or an explicit unavailable state. The two-month fixture is deliberately small enough that you can check all supplied order records. It does not independently prove anything about a real extraction system.

Keep reconciliation visible

The four nonzero month/channel cells are February web €250, February store €250, March web €300 and March store €300. Partner contributes zero in both months. Sum across channels to recover the monthly headline, and across months to get each channel's total. Do not sum ratios such as AOV across grid cells; combine their numerators and denominators instead.

This grid pattern is useful for attendance, operations and sales reporting whenever an explicit list of categories and periods matters. Its value comes from a clear coverage contract, not from converting every NULL to a pleasing number.

Your turn

Practical exercise

Complete Q10 and Q11. Q10 produces monthly completed, returned and cancelled order counts. Q11 produces every month/channel pair with completed sales in euros. Predict the number of output rows before running it. Then explain how the same zero-fill would mislead if the second month were an unreceived extract rather than a complete reporting period.

Deliverable: a two-row status summary, a six-row month/channel grid and a coverage note. Reconcile nonzero cells to Q5 and all status counts to the 12 verified orders.

Open the worked solution

SELECT substr(order_date, 1, 7) AS month,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
       SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned_orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY substr(order_date, 1, 7)
ORDER BY month;

Each month has four completed orders, one returned and one cancelled: six total orders per month and 12 overall.

WITH months(month) AS (VALUES ('2026-02'), ('2026-03')),
monthly_sales AS (
    SELECT substr(order_date, 1, 7) AS month, channel,
           SUM(order_value_cents) AS sales_cents
    FROM orders
    WHERE status = 'completed'
    GROUP BY substr(order_date, 1, 7), channel
)
SELECT m.month, c.channel, COALESCE(s.sales_cents, 0) / 100.0 AS sales_eur
FROM months AS m CROSS JOIN channels AS c
LEFT JOIN monthly_sales AS s ON s.month = m.month AND s.channel = c.channel
ORDER BY m.month, c.channel;

Q11 returns six rows in month/channel order. partner is €0 in both months, store is €250 then €300, and web is €250 then €300. The grid count is 2 × 3 = 6. If a month were unreceived, a zero would conceal missing coverage. Keep that period unavailable or obtain its coverage confirmation before filling the grid.

The result's grain is one month/channel combination. It is not one order. Its sum is meaningful for additive completed sales, but not for an average or percentage copied into each cell.

Check your understanding

One more question.

SQL references: SELECT and joins, aggregate functions, window functions.

How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io

Read and practice without an account. Sign in with an existing account to save lesson completion.

Next lesson →

Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.