SQL GROUP BY and Aggregate Functions MCQ

What this topic covers

31 subtopics

HAVING6
Aggregates on empty input3
GROUP BY with NULL3
Conditional aggregation2
COUNT2
COUNT DISTINCT2
Logical order2
Nested aggregation2
Aggregate of aggregates1
Aggregates and NULL1
Anti-join with aggregation1
AVG1

Show all 31 subtopics

AVG and NULL1
COUNT variants1
DISTINCT and NULL1
GROUP BY1
GROUP BY an expression1
GROUP BY cardinality1
GROUP BY on a joined column1
GROUP BY rules1
GROUP_CONCAT1
LEFT JOIN with COUNT1
MIN and MAX1
MIN and MAX on text1
ORDER BY aggregate1
Percent of total1
Performance1
ROLLUP1
Running total1
SUM1
Two-level grouping1

Beginner 15
Intermediate 15
Advanced 15

About this topic

Aggregate functions summarise many rows into one value per group. Most mistakes come from NULLs: COUNT(*) counts rows while COUNT(column) skips NULLs, AVG ignores NULLs instead of treating them as zero, and SUM of no rows is NULL, not 0. The questions below use a small customers and orders dataset that contains exactly these cases.

Free preview

Sample questions

Try each one first, then open the answer for the explanation and why the other options are wrong.

Question 1 COUNT Beginner Practice

What is the difference between COUNT(*) and COUNT(amount)?

COUNT(amount) counts distinct amounts

COUNT(*) counts all rows; COUNT(amount) counts only rows where amount is not NULL

They always return the same number

COUNT(*) skips rows that contain any NULL

Show answer and explanation

Correct answer B

Explanation

COUNT(*) counts rows. COUNT(expression) counts the rows where the expression is not NULL.

Why the other options are wrong

AThat is COUNT(DISTINCT amount).
CThey differ whenever the column contains NULLs.
DCOUNT(*) never looks at column values.

Question 2 COUNT Beginner Practice Query executed

What does this query return?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT COUNT(*), COUNT(amount), COUNT(customer_id)
FROM orders;

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	7	7

COUNT(*)	COUNT(amount)	COUNT(customer_id)
7	7	7

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	8	8

COUNT(*)	COUNT(amount)	COUNT(customer_id)
8	7	6

Show answer and explanation

Correct answer A

Explanation

There are 8 orders. Order 104 has no amount and order 108 has no customer, so each column count skips one row.

Why the other options are wrong

BCOUNT(*) counts every row, including ones with NULLs.
CCOUNT(column) skips NULLs.
DOnly one order has a NULL customer_id.

Question 3 AVG Beginner Practice Query executed

What does AVG(amount) return (rounded to 4 decimals)?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT AVG(amount) AS average FROM orders;

average
5400

average
900

average
771.4286

average
675

Show answer and explanation

Correct answer C

Explanation

AVG divides the sum by the number of non-NULL values: 5400 / 7 = 771.4286. The NULL amount is ignored, not treated as zero.

Why the other options are wrong

A5400 is the sum.
B900 is not derived from the data.
D675 = 5400 / 8 treats the NULL as 0 – a very common mistake.

Question 4 HAVING Beginner Practice

What is the difference between WHERE and HAVING?

HAVING filters rows before grouping; WHERE filters groups

WHERE filters rows before grouping; HAVING filters groups after aggregation

HAVING can only be used with ORDER BY

They are interchangeable

Show answer and explanation

Correct answer B

Explanation

WHERE decides which rows enter the groups and cannot use aggregates. HAVING runs on the finished groups, so it can test values like COUNT(*) > 1.

Why the other options are wrong

AThis reverses them.
CHAVING belongs with GROUP BY (or a whole-table group).
DAggregate conditions only work in HAVING, and row filters are cheaper in WHERE.

Question 5 Conditional aggregation Intermediate Practice Query executed

What does this conditional aggregation return?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
  SUM(CASE WHEN status <> 'paid' THEN amount ELSE 0 END) AS other
FROM orders;

paid	other
4300	1500

paid	other
4300	NULL

paid	other
4300	1100

paid	other
5400	0

Show answer and explanation

Correct answer C

Explanation

Paid = 500 + 1500 + 1200 + 700 + 400 = 4300. The others are 800 (cancelled) and 300 (refunded); the pending order's amount is NULL and SUM skips it, so other = 1100.

Why the other options are wrong

AThere is no 400 among the non-paid amounts.
BA single NULL does not make SUM NULL.
DThe CASE splits the rows by status.

Question 6 Aggregates on empty input Advanced Practice Query executed

No order is "shipped". Unlike the query without GROUP BY, what does this return?

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meena	Pune
4	John	Mumbai
5	Zoya	NULL

orders

id	customer_id	amount	status	order_date
101	1	500	paid	2026-01-05
102	1	1500	paid	2026-01-20
103	2	800	cancelled	2026-02-02
104	3	NULL	pending	2026-02-10
105	3	1200	paid	2026-02-15
106	4	700	paid	2026-03-01
107	1	300	refunded	2026-03-03
108	NULL	400	paid	2026-03-09

SELECT status, COUNT(*) AS n
FROM orders
WHERE status = 'shipped'
GROUP BY status;

status	n
NULL	0

status	n
shipped	0

status	n
NULL	NULL

status	n
(no rows)

Show answer and explanation

Correct answer D

Explanation

With GROUP BY, the result has one row per group – and zero input rows means zero groups, so no rows at all. Without GROUP BY, the same COUNT(*) returns a single row containing 0. Reports that must show zero counts need a LEFT JOIN from a list of statuses.

Why the other options are wrong

AThat single-row result is what you get without GROUP BY (as 0).
BA group exists only if at least one row has that value.
CCOUNT never returns NULL.

39 more questions in the timed setsSame format as the samples, with a timer, a question palette and a full review at the end.

Start practising

Aggregates and GROUP BY: questions with answers

What this topic covers

About this topic

Sample questions

All 10 topics