The finance team wants to understand how order volume and revenue vary across order statuses.
Using the orders table, prepare a status-level summary showing the total number of orders and the sum of their recorded amounts (amount_usd).
Some orders have no recorded status. These should appear under the status label 'unknown'.
Ordering: Sort by status in ascending order.
Returnstatusorder_counttotal
About this problem
You practise a status-level summary of orders: how many orders sit in each status and what their amount_usd adds up to, returned as status, order_count and total. Orders with no recorded status belong in their own group labelled 'unknown', and the output is sorted by status. The skill is choosing the grouping key, relabelling a missing value so it is visible in the report, and knowing exactly what each aggregate counts.
The first trap is the missing status. GROUP BY status does keep NULL statuses together in one group, but its label is NULL, not 'unknown', and the engine may sort it first or last. The group is there, so the report looks complete at a glance. The second trap is the count. COUNT(status) skips rows where status is NULL, so the unknown group would report 0 orders while still showing a non-zero total, which looks like a data problem rather than a query problem. Finally, adding customer_id to the grouping splits each status into per-customer rows, and the summary stops answering finance’s question.
Read the lesson: Aggregation & GROUP BY (P04) →