Aggregation & Grouping in SQL: Answer WHERE vs HAVING with Confidence
At 8:55 a.m., a city operations manager opens a dashboard: bookings by city, cancelled orders by category, average rating by service professional. None of those numbers exist as individual rows - SQL creates them by grouping thousands of transactions into decision-ready summaries.
- Aggregation converts many rows into one summary value, such as
SUM,COUNT,AVG,MINorMAX. - GROUP BY forms buckets of rows that share the same value, such as city, category, month or customer segment.
- WHERE filters rows before grouping - use it for row-level conditions like
status = 'Completed'. - HAVING filters groups after aggregation - use it for group-level conditions like
COUNT(*) > 100. - If a selected column is not aggregated, it must usually appear in
GROUP BY. - The mental model: raw rows - filter rows - form groups - calculate aggregates - filter groups.
- The common interview trap is using
WHEREto filter an aggregate result.
The Big Picture
Aggregation is how databases answer business questions that begin with “total,” “average,” “count,” “highest,” “lowest,” or “by segment.” The order matters because SQL first decides which rows are eligible, then groups them, then calculates the summary.
Core Explanation: From Raw Rows to Business Summary
Think of a transaction table as a long receipt book. A business leader rarely wants every receipt; they want answers like “Which cities crossed ₹1 lakh in completed revenue?” or “Which categories have more than 50 cancellations?” Aggregation is the SQL mechanism that compresses row-level facts into such summaries.
The basic pattern is:
SELECT group_column, aggregate_function(metric) FROM table WHERE row_condition GROUP BY group_column HAVING group_condition;
The Five Pieces You Must Understand
WHERE versus HAVING: The Clean Comparison
Common Aggregate Functions
A Small Worked Example
Suppose a service marketplace has this simplified bookings table:
Question: “Show cities where completed revenue is more than 10000.”
The correct SQL logic is:
SELECT city, SUM(revenue) AS completed_revenue FROM bookings WHERE status = 'Completed' GROUP BY city HAVING SUM(revenue) > 10000;
Notice the key move: status = 'Completed' belongs in WHERE because it describes individual rows. SUM(revenue) > 10000 belongs in HAVING because it describes the grouped summary.
Definitions
- Aggregation: A database operation that calculates a summary value from multiple rows using functions such as SUM, COUNT or AVG.
- GROUP BY: A SQL clause that combines rows sharing selected column values into groups for aggregate calculation.
- WHERE: A SQL clause that filters individual rows before grouping or aggregation occurs.
- HAVING: A SQL clause that filters grouped rows after aggregate functions have been calculated.
Urban Company: Grouping Marketplace Signals into Operating Decisions
Urban Company shows why aggregation matters: a home-services marketplace cannot manage quality, demand and supply from raw bookings alone.
Situation: Urban Company operates across service categories such as beauty, cleaning, repair and appliance services. Each booking creates granular data: city, category, time slot, professional, status, customer rating and payment. Looking at individual bookings is necessary for issue resolution, but it is too noisy for managerial decisions.
The move: The operating lens becomes grouped data. Teams can group bookings by city to track demand, by category to understand service mix, by professional cohort to monitor quality, and by time slot to balance supply. The primary driver is converting high-volume transaction data into actionable summary views. Supporting drivers include standardized service categories, app-based booking records, rating capture and operational dashboards.
The lesson: Aggregation helps answer questions that directly affect execution: Which cities have rising cancellations? Which service categories need more partner supply? Which professional cohorts need training? The win does not come from “having data” alone; it comes from grouping data at the decision level where action is possible.
So what: A strong analytics answer does not stop at writing GROUP BY. It explains the business grain - city, category, cohort or time - and why that grain matches the decision being made.
How AI Changes Aggregation & Grouping
AI does not remove the need to understand GROUP BY; it makes the analyst more responsible for checking whether the generated query matches the business grain.
The practical edge in 2026 is not “AI can write SQL.” The edge is being able to audit AI-written SQL for silent logic errors - especially wrong filters, wrong grouping grain and duplicate inflation after joins.
Interview Relevance
“What is the difference between WHERE and HAVING? Write a query to find categories with more than 100 completed orders.”
Before writing SQL, say the grain aloud: “One row in my output should be one category.” This single sentence prevents most grouping mistakes.
Common Mistake
The error that costs candidates is treating WHERE and HAVING as interchangeable. It costs you because it reveals you know SQL syntax but not SQL execution logic. One-line fix: use WHERE for row facts; use HAVING for aggregate facts.
What to Revise Next
Once grouping is clear, move to joins - because the most dangerous aggregation errors happen after tables are joined and rows multiply silently.