Report queries: sales per month and top customers

50 min · Intermediate

The manager asks: "How much did we earn each month, and who are our best customers?" One answer must be a small, correct table of totals. How do you build it from raw order rows?

Revenue per month

A report query answers a business question with a small table of numbers, such as revenue per month. It is a GROUP BY with the right filters, aggregates and order, plus a check that the numbers add up.

ShopLite Express keeps one flat table of orders: customer, city, date, amount and status. Only DELIVERED orders count as revenue, so the query removes cancelled ones in WHERE and groups by month.

Example 1 · Monthly revenue for ShopLite Express

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,28       COUNT(*)                         AS orders,29       SUM(amount)                      AS revenue,30       ROUND(AVG(amount), 2)            AS avg_order31FROM shop_orders32WHERE status = 'DELIVERED'33GROUP BY month34ORDER BY month;

Output

+---------+--------+---------+-----------+
| month   | orders | revenue | avg_order |
+---------+--------+---------+-----------+
| 2025-01 |      3 |  830.00 |    276.67 |
| 2025-02 |      4 | 1710.00 |    427.50 |
| 2025-03 |      5 | 1960.00 |    392.00 |
+---------+--------+---------+-----------+

What this code does

WHERE removes the 2 cancelled orders before grouping, so they are in neither the count nor the revenue.
avg_order is the average order value, the revenue divided by the number of delivered orders in that month, rounded to paise.
The '%Y-%m' month text sorts in date order, so ORDER BY month lists January to March.

A month with no sales does not appear at all, because GROUP BY only makes groups from rows that exist; showing it as 0 needs the Joins module. Every example builds the same table first, so each one runs on its own.

Write down what "revenue" means before you query: here, delivered orders only. A report with an unclear definition gives numbers nobody can trust.

Top customers by spending

A top-N report groups by something such as the customer, adds up a number, sorts from high to low and keeps the first N rows with LIMIT. It answers questions like "Who are our 3 best customers?".

Who are our 3 best customers? The first example groups by customer, adds up what each one spent, sorts and cuts with LIMIT 3, and then looks at the tie at the cut-off.

Example 2 · Top 3 customers, and the tie at the cut-off

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27-- All customers, so you can see the 3rd and 4th28SELECT customer, COUNT(*) AS orders, SUM(amount) AS spent29FROM shop_orders30WHERE status = 'DELIVERED'31GROUP BY customer32ORDER BY spent DESC, customer;33
34-- Top 335SELECT customer, COUNT(*) AS orders, SUM(amount) AS spent36FROM shop_orders37WHERE status = 'DELIVERED'38GROUP BY customer39ORDER BY spent DESC, customer40LIMIT 3;

Output

+----------+--------+---------+
| customer | orders | spent   |
+----------+--------+---------+
| Nikhil   |      2 | 1130.00 |
| Sana     |      2 |  970.00 |
| Asha     |      3 |  860.00 |
| Kabir    |      2 |  860.00 |
| Ravi     |      2 |  440.00 |
| Meena    |      1 |  240.00 |
+----------+--------+---------+
+----------+--------+---------+
| customer | orders | spent   |
+----------+--------+---------+
| Nikhil   |      2 | 1130.00 |
| Sana     |      2 |  970.00 |
| Asha     |      3 |  860.00 |
+----------+--------+---------+

What this code does

Asha and Kabir both spent Rs 860. ORDER BY spent DESC, customer puts Asha first by name, so LIMIT 3 keeps her and drops Kabir.
The tie-breaker makes the result the same on every run, but the report should say how ties are handled, or list both.

Example 3 · ShopLite: the 3 biggest orders

SQL

1SELECT order_id,2       SUM(quantity)              AS units,3       SUM(quantity * unit_price) AS order_value4FROM order_items5GROUP BY order_id6ORDER BY order_value DESC, order_id7LIMIT 3;

Output

+----------+-------+-------------+
| order_id | units | order_value |
+----------+-------+-------------+
|        4 |     1 |    69900.00 |
|        8 |     1 |    64990.00 |
|        3 |     2 |    43839.00 |
+----------+-------+-------------+

What this code does

The same top-N pattern on the ShopLite practice tables: group the order lines by order_id, add up their value, sort and keep 3.
Order 4 is the biggest, but it was cancelled. order_items has no status column; filtering by status needs the orders table too, which the Joins module adds.

If the 3rd and 4th customers spent the same amount, LIMIT 3 keeps one and silently drops the other. A tie-breaker in ORDER BY makes the choice fixed, but it is still a choice; the window functions lesson shows DENSE_RANK, which gives both rank 3.

Predict the output

Which order wins?

Read the code and pick what it prints.

Round 1 of 2

Order values are quantity times unit price, per order.

SELECT order_id, SUM(quantity * unit_price) AS value

FROM order_items

GROUP BY order_id

ORDER BY value DESC

LIMIT 1;

Subtotals and grand totals with WITH ROLLUP

WITH ROLLUP after GROUP BY adds extra summary rows: a subtotal for each value of the first grouping column and a grand total at the end. In a summary row, the rolled-up columns show NULL.

A city report with a subtotal per city and a grand total would need three queries glued together. WITH ROLLUP gives all three levels in one result; the second example labels the summary rows with GROUPING().

Example 4 · WITH ROLLUP: subtotals per city and a grand total

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT city,28       DATE_FORMAT(order_date, '%Y-%m') AS month,29       SUM(amount)                      AS revenue30FROM shop_orders31WHERE status = 'DELIVERED'32GROUP BY city, month WITH ROLLUP33ORDER BY GROUPING(city), city, GROUPING(month), month;

Output

+-----------+---------+---------+
| city      | month   | revenue |
+-----------+---------+---------+
| Bengaluru | 2025-01 |  240.00 |
| Bengaluru | 2025-02 |  930.00 |
| Bengaluru | 2025-03 |  660.00 |
| Bengaluru | NULL    | 1830.00 |
| Chennai   | 2025-01 |  180.00 |
| Chennai   | 2025-02 |  260.00 |
| Chennai   | 2025-03 |  240.00 |
| Chennai   | NULL    |  680.00 |
| Delhi     | 2025-01 |  410.00 |
| Delhi     | 2025-02 |  520.00 |
| Delhi     | 2025-03 | 1060.00 |
| Delhi     | NULL    | 1990.00 |
| NULL      | NULL    | 4500.00 |
+-----------+---------+---------+

What this code does

Each city has its 3 month rows, then a row with month = NULL: that city's subtotal. The last row, with both columns NULL, is the grand total.
ORDER BY GROUPING(city), city, GROUPING(month), month puts the detail rows (0) before the summary rows (1) inside each level. Sorting by city, month alone would move the NULL rows to the top.

Example 5 · Readable labels with GROUPING()

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT IF(GROUPING(city), 'All cities', city) AS city,28       COUNT(*)                                AS orders,29       SUM(amount)                             AS revenue30FROM shop_orders31WHERE status = 'DELIVERED'32GROUP BY city WITH ROLLUP33ORDER BY GROUPING(city), revenue DESC;

Output

+------------+--------+---------+
| city       | orders | revenue |
+------------+--------+---------+
| Delhi      |      4 | 1990.00 |
| Bengaluru  |      5 | 1830.00 |
| Chennai    |      3 |  680.00 |
| All cities |     12 | 4500.00 |
+------------+--------+---------+

What this code does

GROUPING(city) is 1 only in the rollup row, so IF shows All cities there and the real city everywhere else.
Ordering by GROUPING(city) first keeps the total at the bottom; revenue DESC sorts the cities inside the detail rows.

GROUPING(column) returns 1 in a summary row where that column was rolled up, and 0 otherwise. It needs MySQL 8.0, and ORDER BY together with ROLLUP needs 8.0.12 or later. PostgreSQL writes it as GROUP BY ROLLUP (city, month).

Do not label rollup rows with COALESCE(city, 'All cities'). If some real rows have a NULL city, they would be labelled as a total too. GROUPING() tells the two apart.

Turning a business question into GROUP BY

Most report requests fit the same six steps. The example answers one request clause by clause, with a comment for each step, and then checks the total against the raw data.

One row of the answer is one what? A city, a month, a customer → that goes in GROUP BY.
Which numbers does each row need? Revenue, orders, customers → SUM, COUNT, COUNT(DISTINCT ...).
Which rows count? Delivered only, this quarter only → WHERE.
Which groups should be shown? Above Rs 1,000, at least 2 orders → HAVING.
In what order, and how many? → ORDER BY with a tie-breaker, then LIMIT.
Check the result against the raw data: a single SUM over the same rows must equal the report's total.

Example 6 · From question to query, then check it

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27-- Question: which cities brought more than Rs 1,000 of delivered revenue28-- in Q1 2025, from how many customers? Biggest city first.29SELECT city,                                     -- one row per city30       COUNT(DISTINCT customer) AS customers,    -- numbers per row31       SUM(amount)              AS revenue32FROM shop_orders33WHERE status = 'DELIVERED'                       -- which rows count34  AND order_date BETWEEN '2025-01-01' AND '2025-03-31'35GROUP BY city36HAVING SUM(amount) > 1000                        -- which groups to show37ORDER BY revenue DESC, city;                     -- order38
39-- Check: the same rows, no grouping. Must equal all cities added up.40SELECT COUNT(*) AS delivered_orders, SUM(amount) AS delivered_revenue41FROM shop_orders42WHERE status = 'DELIVERED'43  AND order_date BETWEEN '2025-01-01' AND '2025-03-31';

Output

+-----------+-----------+---------+
| city      | customers | revenue |
+-----------+-----------+---------+
| Delhi     |         2 | 1990.00 |
| Bengaluru |         2 | 1830.00 |
+-----------+-----------+---------+
+------------------+-------------------+
| delivered_orders | delivered_revenue |
+------------------+-------------------+
|               12 |           4500.00 |
+------------------+-------------------+

What this code does

Each comment matches one of the six steps. Chennai (Rs 680) is dropped by HAVING, so the report shows 2 cities.
The check query uses the same WHERE and no GROUP BY. Delhi 1,990 + Bengaluru 1,830 + Chennai 680 = 4,500, which matches delivered_revenue. If it did not, a filter or a group would be wrong.

The last step is the one people skip. If the totals do not match, a filter or a grouping is wrong, and it is better that you find it than the person reading the report.

Next step

Check your understanding

11 quick questions

Report queries: sales per month and top customers

50 min · Intermediate

Need help? Ask a tutor

The manager asks: "How much did we earn each month, and who are our best customers?" One answer must be a small, correct table of totals. How do you build it from raw order rows?

Revenue per month

ShopLite Express keeps one flat table of orders: customer, city, date, amount and status. Only DELIVERED orders count as revenue, so the query removes cancelled ones in WHERE and groups by month.

Example 1 · Monthly revenue for ShopLite Express

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,28       COUNT(*)                         AS orders,29       SUM(amount)                      AS revenue,30       ROUND(AVG(amount), 2)            AS avg_order31FROM shop_orders32WHERE status = 'DELIVERED'33GROUP BY month34ORDER BY month;

Output

+---------+--------+---------+-----------+
| month   | orders | revenue | avg_order |
+---------+--------+---------+-----------+
| 2025-01 |      3 |  830.00 |    276.67 |
| 2025-02 |      4 | 1710.00 |    427.50 |
| 2025-03 |      5 | 1960.00 |    392.00 |
+---------+--------+---------+-----------+

What this code does

WHERE removes the 2 cancelled orders before grouping, so they are in neither the count nor the revenue.
avg_order is the average order value, the revenue divided by the number of delivered orders in that month, rounded to paise.
The '%Y-%m' month text sorts in date order, so ORDER BY month lists January to March.

Write down what "revenue" means before you query: here, delivered orders only. A report with an unclear definition gives numbers nobody can trust.

Top customers by spending

A top-N report groups by something such as the customer, adds up a number, sorts from high to low and keeps the first N rows with LIMIT. It answers questions like "Who are our 3 best customers?".

Who are our 3 best customers? The first example groups by customer, adds up what each one spent, sorts and cuts with LIMIT 3, and then looks at the tie at the cut-off.

Example 2 · Top 3 customers, and the tie at the cut-off

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27-- All customers, so you can see the 3rd and 4th28SELECT customer, COUNT(*) AS orders, SUM(amount) AS spent29FROM shop_orders30WHERE status = 'DELIVERED'31GROUP BY customer32ORDER BY spent DESC, customer;33
34-- Top 335SELECT customer, COUNT(*) AS orders, SUM(amount) AS spent36FROM shop_orders37WHERE status = 'DELIVERED'38GROUP BY customer39ORDER BY spent DESC, customer40LIMIT 3;

Output

+----------+--------+---------+
| customer | orders | spent   |
+----------+--------+---------+
| Nikhil   |      2 | 1130.00 |
| Sana     |      2 |  970.00 |
| Asha     |      3 |  860.00 |
| Kabir    |      2 |  860.00 |
| Ravi     |      2 |  440.00 |
| Meena    |      1 |  240.00 |
+----------+--------+---------+
+----------+--------+---------+
| customer | orders | spent   |
+----------+--------+---------+
| Nikhil   |      2 | 1130.00 |
| Sana     |      2 |  970.00 |
| Asha     |      3 |  860.00 |
+----------+--------+---------+

What this code does

Asha and Kabir both spent Rs 860. ORDER BY spent DESC, customer puts Asha first by name, so LIMIT 3 keeps her and drops Kabir.
The tie-breaker makes the result the same on every run, but the report should say how ties are handled, or list both.

Example 3 · ShopLite: the 3 biggest orders

SQL

1SELECT order_id,2       SUM(quantity)              AS units,3       SUM(quantity * unit_price) AS order_value4FROM order_items5GROUP BY order_id6ORDER BY order_value DESC, order_id7LIMIT 3;

Output

+----------+-------+-------------+
| order_id | units | order_value |
+----------+-------+-------------+
|        4 |     1 |    69900.00 |
|        8 |     1 |    64990.00 |
|        3 |     2 |    43839.00 |
+----------+-------+-------------+

What this code does

The same top-N pattern on the ShopLite practice tables: group the order lines by order_id, add up their value, sort and keep 3.
Order 4 is the biggest, but it was cancelled. order_items has no status column; filtering by status needs the orders table too, which the Joins module adds.

Predict the output

Which order wins?

Read the code and pick what it prints.

Round 1 of 2

Order values are quantity times unit price, per order.

SELECT order_id, SUM(quantity * unit_price) AS value

FROM order_items

GROUP BY order_id

ORDER BY value DESC

LIMIT 1;

Subtotals and grand totals with WITH ROLLUP

WITH ROLLUP after GROUP BY adds extra summary rows: a subtotal for each value of the first grouping column and a grand total at the end. In a summary row, the rolled-up columns show NULL.

Example 4 · WITH ROLLUP: subtotals per city and a grand total

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT city,28       DATE_FORMAT(order_date, '%Y-%m') AS month,29       SUM(amount)                      AS revenue30FROM shop_orders31WHERE status = 'DELIVERED'32GROUP BY city, month WITH ROLLUP33ORDER BY GROUPING(city), city, GROUPING(month), month;

Output

+-----------+---------+---------+
| city      | month   | revenue |
+-----------+---------+---------+
| Bengaluru | 2025-01 |  240.00 |
| Bengaluru | 2025-02 |  930.00 |
| Bengaluru | 2025-03 |  660.00 |
| Bengaluru | NULL    | 1830.00 |
| Chennai   | 2025-01 |  180.00 |
| Chennai   | 2025-02 |  260.00 |
| Chennai   | 2025-03 |  240.00 |
| Chennai   | NULL    |  680.00 |
| Delhi     | 2025-01 |  410.00 |
| Delhi     | 2025-02 |  520.00 |
| Delhi     | 2025-03 | 1060.00 |
| Delhi     | NULL    | 1990.00 |
| NULL      | NULL    | 4500.00 |
+-----------+---------+---------+

What this code does

Each city has its 3 month rows, then a row with month = NULL: that city's subtotal. The last row, with both columns NULL, is the grand total.
ORDER BY GROUPING(city), city, GROUPING(month), month puts the detail rows (0) before the summary rows (1) inside each level. Sorting by city, month alone would move the NULL rows to the top.

Example 5 · Readable labels with GROUPING()

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27SELECT IF(GROUPING(city), 'All cities', city) AS city,28       COUNT(*)                                AS orders,29       SUM(amount)                             AS revenue30FROM shop_orders31WHERE status = 'DELIVERED'32GROUP BY city WITH ROLLUP33ORDER BY GROUPING(city), revenue DESC;

Output

+------------+--------+---------+
| city       | orders | revenue |
+------------+--------+---------+
| Delhi      |      4 | 1990.00 |
| Bengaluru  |      5 | 1830.00 |
| Chennai    |      3 |  680.00 |
| All cities |     12 | 4500.00 |
+------------+--------+---------+

What this code does

GROUPING(city) is 1 only in the rollup row, so IF shows All cities there and the real city everywhere else.
Ordering by GROUPING(city) first keeps the total at the bottom; revenue DESC sorts the cities inside the detail rows.

Do not label rollup rows with COALESCE(city, 'All cities'). If some real rows have a NULL city, they would be labelled as a total too. GROUPING() tells the two apart.

Turning a business question into GROUP BY

Most report requests fit the same six steps. The example answers one request clause by clause, with a comment for each step, and then checks the total against the raw data.

One row of the answer is one what? A city, a month, a customer → that goes in GROUP BY.
Which numbers does each row need? Revenue, orders, customers → SUM, COUNT, COUNT(DISTINCT ...).
Which rows count? Delivered only, this quarter only → WHERE.
Which groups should be shown? Above Rs 1,000, at least 2 orders → HAVING.
In what order, and how many? → ORDER BY with a tie-breaker, then LIMIT.
Check the result against the raw data: a single SUM over the same rows must equal the report's total.

Example 6 · From question to query, then check it

SQL

1DROP TABLE IF EXISTS shop_orders;2CREATE TABLE shop_orders (3  order_id   INT PRIMARY KEY,4  customer   VARCHAR(20)  NOT NULL,5  city       VARCHAR(20)  NOT NULL,6  order_date DATE         NOT NULL,7  amount     DECIMAL(8,2) NOT NULL,8  status     VARCHAR(10)  NOT NULL9);10
11INSERT INTO shop_orders VALUES12  (1,  'Asha',   'Bengaluru', '2025-01-04', 240.00, 'DELIVERED'),13  (2,  'Ravi',   'Chennai',   '2025-01-06', 180.00, 'DELIVERED'),14  (3,  'Kabir',  'Delhi',     '2025-01-20', 410.00, 'DELIVERED'),15  (4,  'Meena',  'Chennai',   '2025-01-25', 150.00, 'CANCELLED'),16  (5,  'Asha',   'Bengaluru', '2025-02-08', 290.00, 'DELIVERED'),17  (6,  'Nikhil', 'Delhi',     '2025-02-12', 520.00, 'DELIVERED'),18  (7,  'Ravi',   'Chennai',   '2025-02-19', 260.00, 'DELIVERED'),19  (8,  'Sana',   'Bengaluru', '2025-02-25', 640.00, 'DELIVERED'),20  (9,  'Asha',   'Bengaluru', '2025-03-03', 330.00, 'DELIVERED'),21  (10, 'Kabir',  'Delhi',     '2025-03-07', 450.00, 'DELIVERED'),22  (11, 'Nikhil', 'Delhi',     '2025-03-10', 610.00, 'DELIVERED'),23  (12, 'Meena',  'Chennai',   '2025-03-18', 240.00, 'DELIVERED'),24  (13, 'Sana',   'Bengaluru', '2025-03-22', 330.00, 'DELIVERED'),25  (14, 'Nikhil', 'Delhi',     '2025-03-29', 700.00, 'CANCELLED');26
27-- Question: which cities brought more than Rs 1,000 of delivered revenue28-- in Q1 2025, from how many customers? Biggest city first.29SELECT city,                                     -- one row per city30       COUNT(DISTINCT customer) AS customers,    -- numbers per row31       SUM(amount)              AS revenue32FROM shop_orders33WHERE status = 'DELIVERED'                       -- which rows count34  AND order_date BETWEEN '2025-01-01' AND '2025-03-31'35GROUP BY city36HAVING SUM(amount) > 1000                        -- which groups to show37ORDER BY revenue DESC, city;                     -- order38
39-- Check: the same rows, no grouping. Must equal all cities added up.40SELECT COUNT(*) AS delivered_orders, SUM(amount) AS delivered_revenue41FROM shop_orders42WHERE status = 'DELIVERED'43  AND order_date BETWEEN '2025-01-01' AND '2025-03-31';

Output

+-----------+-----------+---------+
| city      | customers | revenue |
+-----------+-----------+---------+
| Delhi     |         2 | 1990.00 |
| Bengaluru |         2 | 1830.00 |
+-----------+-----------+---------+
+------------------+-------------------+
| delivered_orders | delivered_revenue |
+------------------+-------------------+
|               12 |           4500.00 |
+------------------+-------------------+

What this code does

Each comment matches one of the six steps. Chennai (Rs 680) is dropped by HAVING, so the report shows 2 cities.
The check query uses the same WHERE and no GROUP BY. Delhi 1,990 + Bengaluru 1,830 + Chennai 680 = 4,500, which matches delivered_revenue. If it did not, a filter or a group would be wrong.

The last step is the one people skip. If the totals do not match, a filter or a grouping is wrong, and it is better that you find it than the person reading the report.

Next step

Check your understanding

11 quick questions