GROUP BY — SQL Technical Interview Questions & Answers

GROUP BY

GROUP BY

  • GROUP BY city collapses rows that share a city. Then you use aggregates: SELECT city, COUNT(*), AVG(marks) FROM students GROUP BY city;
  • Every selected column must be in GROUP BY or inside COUNT/SUM/AVG/MIN/MAX (ONLY_FULL_GROUP_BY). SELECT name, AVG(marks) GROUP BY city is illegal — which name?
  • WHERE filters rows before groups. HAVING filters groups after: HAVING AVG(marks) < 40.
  • Trap: SELECT * with GROUP BY. Trap: using WHERE AVG(marks) < 40.
  • On the example next to this theory: GROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Technical Interview Tip: SELECT city, AVG(marks) FROM students GROUP BY city;

Code Examples for GROUP BY

GROUP BY — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

GROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

How to Explain GROUP BY to an Interviewer

What does GROUP BY do?

One row per group Use with COUNT/AVG/SUM

WHERE vs HAVING?

WHERE before groups HAVING after aggregates