SQL GROUP BY Quiz: Aggregates, HAVING and Pivot Practice Test
Test yourself: 12 questions and three queries on COUNT, SUM and AVG with NULLs, GROUP BY, HAVING, conditional aggregation, group concat and grouping mistakes.
- Course: SQL study plan
- Module: Aggregation
- Kind: Checkpoint — cleared at 70%
- Reading time: 25 min
- Runtime: MySQL 8 syntax on SQLite 3.49
Checkpoint — Aggregation is the checkpoint that closes the Aggregation module: a graded quiz and whole-program exercises, passed at 70%.
Instructions
This checkpoint covers the whole module: the aggregate functions and how they treat NULL and empty input; GROUP BY on columns and expressions; HAVING and how it differs from WHERE; conditional aggregation with SUM(CASE …) for counts, pivots and rates; GROUP_CONCAT with DISTINCT, ORDER BY and SEPARATOR; and the grouping mistakes — ONLY_FULL_GROUP_BY, the three COUNTs, and averages of averages.
How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.
Before you start, make sure you can answer these from memory:
- What do
COUNT(*),COUNT(col)andCOUNT(DISTINCT col)each count? - What do
SUMandCOUNTreturn when no rows match? - What may appear in the SELECT list of a grouped query, and what does error 1055 mean?
- In what order do
WHERE,GROUP BY,HAVING,SELECTandORDER BYrun? - How do you find values that appear more than once in a column?
- Why does
COUNT(CASE WHEN … THEN 1 ELSE 0 END)count every row? - Where do
DISTINCT,ORDER BYandSEPARATORgo in aGROUP_CONCATcall? - Why is an average of averages usually wrong, and what do you compute instead?
The three queries are a monthly sales summary with a revenue floor, a transaction report with approved counts and amounts per month and country, and a daily product list with distinct counts and sorted names.
Common questions
What is the difference between COUNT(*) and COUNT(col)?
COUNT(*) counts rows, whatever they contain; COUNT(col) counts only the rows where col is not NULL. COUNT(DISTINCT col) goes further and counts the different non-NULL values. Say which noun you are counting before choosing one.
When does a condition go in WHERE and when in HAVING?
A condition on individual rows that needs no aggregate goes in WHERE, which runs before grouping and reduces the work. A condition on a group, using COUNT, SUM, MIN or another aggregate, can only go in HAVING, which runs after grouping.
How do you count several statuses per group in one query?
Use one conditional aggregate per status, such as SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid, side by side in the SELECT list with a GROUP BY on the row label. Every group stays in the result, and a status with no rows shows 0.
Exercises
Monthly sales with a revenue floor
Summarise sales per month, leaving out rows from the test region (a NULL region is a real sale and counts), and keep only months whose revenue is at least 100.
| id | sold_on | amount | region |
|---|---|---|---|
| 1 | 2024-01-04 | 40 | north |
| 2 | 2024-01-18 | 70 | south |
| 3 | 2024-01-20 | 500 | test |
| 4 | 2024-02-02 | 30 | north |
| 5 | 2024-02-10 | 50 | north |
| 6 | 2024-03-05 | 120 | south |
| 7 | 2024-03-25 | 15 | north |
| 8 | 2024-02-12 | 900 | test |
Return month (2024-01), sales, revenue, avg_sale (rounded to 2 decimals) and biggest. Order by month.
month | sales | revenue | avg_sale | biggest
2024-01 | 2 | 110 | 55 | 70
2024-03 | 2 | 135 | 67.5 | 120Monthly transactions report
For each month and country, report the transactions and the approved ones.
| id | country | state | amount | trans_date |
|---|---|---|---|---|
| 121 | US | approved | 1000 | 2018-12-18 |
| 122 | US | declined | 2000 | 2018-12-19 |
| 123 | US | approved | 2000 | 2019-01-01 |
| 124 | DE | approved | 2000 | 2019-01-07 |
Return month (2018-12), country, trans_count, approved_count, trans_total_amount and approved_total_amount (0 when nothing was approved). country may be NULL; those rows form their own group. Order by month, then country.
month | country | trans_count | approved_count | trans_total_amount | approved_total_amount
2018-12 | US | 2 | 1 | 3000 | 1000
2019-01 | DE | 1 | 1 | 2000 | 2000
2019-01 | US | 1 | 1 | 2000 | 2000Busy days and what sold
From orders, list the days on which at least two different customers ordered.
| order_date | customer | product |
|---|---|---|
| 2024-04-01 | ana | pen |
| 2024-04-01 | raj | ink |
| 2024-04-01 | ana | ink |
| 2024-04-02 | raj | pad |
| 2024-04-02 | raj | pen |
| 2024-04-03 | li | pen |
| 2024-04-03 | ana | pad |
| 2024-04-03 | raj | pad |
Return order_date, customers (different customers that day) and products (the different products, alphabetical, separated by , ). Order by order_date.
order_date | customers | products
2024-04-01 | 2 | ink, pen
2024-04-03 | 3 | pad, penIn this module: Aggregation
- Aggregate functions — COUNT, SUM, AVG, MIN and MAX
- GROUP BY — one summary row per group
- HAVING — filtering groups
- Conditional aggregation — SUM(CASE …) and pivots
- GROUP_CONCAT — a group's values as one string
- Grouping pitfalls — ONLY_FULL_GROUP_BY, COUNT and averages
- Checkpoint — Aggregation (this lesson)
← Grouping pitfalls — ONLY_FULL_GROUP_BY, COUNT and averages · INNER JOIN — matching rows across tables →