How Does GROUP BY Work in SQL?
Learn how SQL GROUP BY collapses rows into summaries, works with aggregate functions and HAVING, plus examples and common interview mistakes to avoid.
Expected Interview Answer
GROUP BY collapses rows that share the same values in the listed columns into a single summary row, letting aggregate functions like COUNT, SUM, and AVG compute one result per group.
Logically the engine first filters rows with WHERE, then partitions the surviving rows into buckets keyed by the GROUP BY columns, and finally applies each aggregate function once per bucket. Every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function, otherwise the query is ambiguous. HAVING can filter the resulting groups after aggregation.
- Produces per-category summaries from raw rows
- Powers reports like totals, averages and counts
- Works with HAVING to filter aggregated results
- Reduces large datasets into meaningful metrics
- Supports grouping on multiple columns at once
AI Mentor Explanation
Think of a full match scorecard with one line per delivery. GROUP BY batter turns that ball-by-ball log into one row per player, and SUM(runs) tallies each batter's total. You go from hundreds of delivery rows to a tidy eleven-row summary showing exactly how many runs each player scored.
Step-by-Step Explanation
Step 1
Filter rows first
WHERE runs before grouping, so only rows passing the condition reach the buckets.
Step 2
Partition into groups
The engine splits surviving rows into buckets, one per distinct combination of GROUP BY columns.
Step 3
Apply aggregates
Each aggregate function (COUNT, SUM, AVG, MIN, MAX) runs once per bucket.
Step 4
Filter groups with HAVING
HAVING removes whole groups based on aggregate conditions, unlike WHERE which filters rows.
Step 5
Project and order
SELECT returns grouped columns and aggregates, and ORDER BY sorts the final summary rows.
What Interviewer Expects
- Knowing WHERE filters before grouping, HAVING after
- The SELECT-must-be-grouped-or-aggregated rule
- Correct use of aggregate functions per group
- Grouping on multiple columns
- A clear real-world example of summarizing rows
Common Mistakes
- Putting a non-aggregated, non-grouped column in SELECT
- Using WHERE to filter aggregates instead of HAVING
- Assuming GROUP BY sorts the output automatically
- Forgetting NULLs form their own group
- Confusing COUNT(*) with COUNT(column) on nullable columns
Best Answer (HR Friendly)
“GROUP BY takes a big list of detailed records and squeezes rows that belong together into a single summary line, so you can count or total each group. For example, it can turn a list of individual sales into total sales per region.”
Code Example
SELECT region,
COUNT(*) AS order_count,
SUM(revenue) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY region
HAVING SUM(revenue) > 10000
ORDER BY total_revenue DESC;Follow-up Questions
- What is the difference between WHERE and HAVING?
- Can you GROUP BY multiple columns, and how does that change the buckets?
- How are NULL values handled by GROUP BY?
- What is the logical order of execution of a SELECT query?
- How do window functions differ from GROUP BY aggregation?
MCQ Practice
1. Which clause filters rows BEFORE they are grouped?
WHERE runs before grouping and filters individual rows; HAVING runs after grouping and filters whole groups.
2. A column in SELECT that is not inside an aggregate function must also appear in?
Every non-aggregated SELECT column must be listed in GROUP BY, otherwise the value for the group is ambiguous.
3. How many rows does GROUP BY region produce?
GROUP BY produces exactly one summary row for each distinct combination of the grouping column values.
Flash Cards
What does GROUP BY do? — Collapses rows sharing the same grouped column values into one summary row for aggregation.
Order of WHERE vs GROUP BY? — WHERE filters rows first, then GROUP BY partitions the surviving rows into buckets.
SELECT column rule with GROUP BY? — Each selected column must be grouped or wrapped in an aggregate function.
How are NULLs grouped? — All NULLs in a grouping column collapse into a single group of their own.