Writing reports with aggregation

From question to query

Business people do not ask for GROUP BY; they ask "How did we do each month?" Before typing, answer four small questions. Your query almost writes itself after that.

1. What is one row of the answer? (one month, one category, one city)
2. Which rows count? (not cancelled orders, only 2025): this becomes WHERE.
3. What do we group by? (the month, the category): this becomes GROUP BY.
4. What number do we want per group? (sum of amount, count of orders): this becomes the aggregate. Then: which groups to keep (HAVING), how to sort (ORDER BY), and how many to show (LIMIT).

The practice data: a sales view

Revenue questions need the order date and status from orders together with qty and price from order_items. Combining tables is a JOIN (module 5). For now, the tasks below give you a ready-made view called sales: a saved query that behaves like a table, with one row per order line and the columns order_id, order_date, status, city, product, category, qty, price and amount (= qty × price). Views are covered in module 10.

Monthly revenue

SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
FROM sales
WHERE status <> 'Cancelled'
GROUP BY month
ORDER BY month;
-- 2025-01 | 3779
-- 2025-02 | 2248
-- 2025-03 | 7167
-- 2025-04 | 650

The top category

SELECT category, SUM(amount) AS revenue
FROM sales
WHERE status <> 'Cancelled'
GROUP BY category
ORDER BY revenue DESC
LIMIT 1;
-- Fashion | 5196

Shares and percentages

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) / COUNT(*), 1)
       AS delivered_pct
FROM orders;
-- 50   (4 of 8 orders)

Say clearly what the percentage is "of". 50% of orders are delivered, but measured by value, delivered orders are 64.4% of everything ordered, because the delivered orders are larger. Both are correct answers to different questions.

Check that totals add up

A report that does not add up loses trust fast. After grouping, compare the sum of the groups with a total worked out separately. Here the four months add to 13844, and SELECT SUM(amount) FROM sales WHERE status <> 'Cancelled' also gives 13844. Category shares should add to about 100% (rounding can give 99.9 or 100.1). If they do not, a filter or a group is wrong.

Labels and rounding

Name every column for the reader (revenue, delivered_pct, avg_order), round money to 2 decimals and percentages to 1, and keep units clear: COMPANY salaries are monthly, so say "monthly" or multiply by 12 and say "annual".

Common mistakes

• Counting after a filter: if WHERE status = 'Delivered' runs first, then SUM(...)/COUNT(*) gives 100%, because only delivered rows are left. Count the part with CASE and the whole with COUNT(*) in the same query, without that WHERE.
• Averaging averages. The average of the three course averages is 72.22, but the average over all students with marks is 72. Groups of different sizes get equal weight when you average averages. Go back to the rows: SUM / COUNT over everything.
• Counting lines instead of orders. sales has 12 rows but only 8 orders; use COUNT(DISTINCT order_id) for orders.
• Forgetting to exclude cancelled orders from revenue, or excluding them from one number but not another in the same report.
• Sorting months as text like "Mar 2025": they sort alphabetically. Group and sort by 'YYYY-MM', and format names only for display.