SQL Topic
GROUP BY
GROUP BY collapses rows that share column values and lets you run aggregate functions (COUNT, SUM, AVG, MIN, MAX) per group. Most interview questions that mention 'per X', 'each Y', or 'by Z' reduce to a GROUP BY.
When is GROUP BY used?
Counting occurrences, computing totals per category, de-duplicating with COUNT(DISTINCT), and any 'per group' aggregation.
Core syntax
SELECT col, COUNT(*) FROM table WHERE ... GROUP BY col — every non-aggregated column in the SELECT must appear in GROUP BY.Common mistakes
- Selecting columns that are neither grouped nor aggregated.
- Filtering groups with WHERE instead of HAVING.
- Assuming GROUP BY orders output (it does not — add ORDER BY).
- Forgetting that COUNT(*) counts rows while COUNT(col) ignores NULLs.
Practice GROUP BY questions
Work through them in order — each question builds on the last.
- easyActive Users Per Day
- easyClasses With At Least 5 Students
- easyDuplicate Emails
- easySubjects Taught By Each Teacher
- easyVisits Without Transactions
- mediumCustomers Who Bought All Products
- mediumDepartments by Average Salary
- mediumMonthly Sales Ranking
- mediumPivot Quarterly Sales
- mediumUsers With the Most Friends