SQL GROUP BY Interview Questions

Q1: Why does this GROUP BY query fail?

SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id;
-- Error: column "order_date" must appear in GROUP BY
-- or be used in an aggregate function

The database doesn't pick an arbitrary row's order_date for you — it refuses to run the query. Once rows are collapsed by customer_id, order_date has no single well-defined value per group unless it's also grouped or wrapped in an aggregate like MAX(order_date). Full explanation in SQL GROUP BY Without Aggregate Function.

Q2: WHERE and HAVING in the same query

SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 3;

WHERE removes non-completed orders before any counting happens. GROUP BY then collapses what's left into one row per customer. HAVING filters those already-grouped rows down to customers with more than 3 completed orders. Swapping the two — putting status = 'completed' in HAVING instead — would force the database to group every row, completed or not, before throwing most of that work away. See Why HAVING Is Used After GROUP BY for exactly why the order is fixed this way.

Q3: GROUP BY multiple columns

SELECT region, status, COUNT(*)
FROM orders
GROUP BY region, status;

The result has one row per distinct (region, status) pair, not one row per region — West appears twice because it has two different status values, each forming its own group.

Q4: Find duplicate rows with GROUP BY

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

This is the standard duplicate-finding pattern: group by the column that defines a "duplicate," then keep only the groups with more than one row. It's also a natural companion to the self-join approach in SQL Tricky Interview Questions With Answers, which returns the actual duplicate row pairs rather than just the count.

Q5: What does ROLLUP add?

SELECT region, status, COUNT(*)
FROM orders
GROUP BY ROLLUP(region, status);

ROLLUP layers extra summary rows on top of the normal grouped output — a subtotal per region (status = NULL marks the subtotal row) and a final grand total row, all in one query instead of a manual UNION of several separately-aggregated queries. More patterns like this in Advanced GROUP BY Techniques You Should Know.

Q6: GROUP BY vs DISTINCT

Without any aggregate function, GROUP BY column and SELECT DISTINCT column return identical results — both simply collapse rows down to one per distinct value. They diverge the moment an aggregate is needed, since only GROUP BY can pair the grouped column with a per-group computed value like COUNT(). Full comparison in SQL GROUP BY Without Aggregate Function.