SQL GROUP BY and HAVING Explained: Difference Between WHERE and HAVING with Examples
Struggling to remember whether to use WHERE or HAVING in SQL? Here is a clear, beginner-friendly guide with real retail aggregation examples.
Curriculum & Analytics Mentorship Faculty
- •GROUP BY collapses rows with identical values in specified columns into summary rows for aggregate functions like SUM, COUNT, and AVG.
- •WHERE filters raw individual records BEFORE grouping happens; it cannot filter aggregate values.
- •HAVING filters aggregated groups AFTER GROUP BY executes; it evaluates summary metrics like COUNT(*) > 5.
- •Every non-aggregated column in your SELECT statement must appear in the GROUP BY clause in standard SQL.
One of the most frequent technical interview questions for junior data analysts in India is: "What is the difference between WHERE and HAVING in SQL?" While the question sounds theoretical, mastering this distinction is essential for writing accurate business intelligence reports.
Whether calculating total revenue per store location or identifying corporate accounts with more than 10 return requests, understanding how SQL evaluates row-level conditions versus group-level conditions prevents costly reporting mistakes.
What Does GROUP BY Actually Do?
Relational databases store transactional records row by row. An e-commerce orders table contains an entry for every purchase. Executives do not need 50,000 raw lines; they need to know total revenue per product category.
GROUP BY takes thousands of individual rows, buckets them by distinct category values, and allows aggregate functions (COUNT, SUM, AVG, MIN, MAX) to calculate statistics for each bucket.
SELECT
category,
COUNT(order_id) AS total_orders,
SUM(order_value) AS total_revenue,
ROUND(AVG(order_value), 2) AS average_order_value
FROM orders
GROUP BY category;WHERE vs HAVING: Execution Order Explained
To understand why both clauses exist, examine SQL’s internal order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
| Clause | When It Filters | What It Filters | Can Use Aggregates? |
|---|---|---|---|
| WHERE | Before grouping happens | Individual rows | No (e.g., WHERE SUM(sales) > 1000 is invalid) |
| HAVING | After grouping finishes | Aggregated buckets | Yes (e.g., HAVING COUNT(*) >= 5 is valid) |
Key operational differences between SQL WHERE and HAVING clauses.
Real E-Commerce Aggregation Queries
Here is a realistic business reporting query that utilizes BOTH WHERE and HAVING in harmony. The business goal: "Find cities where delivered orders in 2026 generated over ₹5,00,000 in revenue."
SELECT
delivery_city,
COUNT(order_id) AS successful_orders,
SUM(total_amount) AS city_gross_revenue
FROM customer_orders
-- Step 1: Filter individual rows BEFORE grouping (delivered status and current year)
WHERE order_status = 'Delivered'
AND order_date >= '2026-01-01'
-- Step 2: Bucket transactions by city
GROUP BY delivery_city
-- Step 3: Filter the aggregated cities AFTER grouping
HAVING SUM(total_amount) > 500000
-- Step 4: Sort by top earning city
ORDER BY city_gross_revenue DESC;Grouping by Multiple Columns
You can group by more than one dimension. For example, grouping by `region` and `order_year` produces summary rows for every unique region-year pairing.
SELECT
region,
EXTRACT(YEAR FROM order_date) AS order_year,
SUM(amount) AS annual_revenue
FROM orders
GROUP BY region, EXTRACT(YEAR FROM order_date)
ORDER BY region, order_year;Common Mistakes & Troubleshooting
Keep these principles in mind when constructing aggregate queries:
- Non-aggregated column error: Selecting a column like `customer_name` without placing it in GROUP BY or wrapping it in an aggregate function will cause a database compilation error in PostgreSQL and SQL Server.
- Using HAVING when WHERE was faster: If you can filter rows before grouping (like filtering by status or date), always use WHERE. It eliminates rows early, making GROUP BY dramatically faster.
- Confusing COUNT(*) with COUNT(column): COUNT(*) counts all rows including NULLs, while COUNT(column) ignores NULL entries.
Frequently Asked Questions
Common queries answered by SSSAM Academy mentors.
Can you use HAVING without GROUP BY in SQL?
Yes, in SQL standard syntax you can use HAVING without GROUP BY. When you do, the entire table is treated as a single group. For example, SELECT AVG(price) FROM products HAVING AVG(price) > 500;
Why does WHERE SUM(sales) > 1000 throw an error in SQL?
Because WHERE evaluates rows one-by-one before any groups or sums exist in memory. The database cannot evaluate SUM(sales) until all qualifying rows have been grouped together, which is why aggregate conditions must go in HAVING.
Authored by SSSAM Academy Academic Team
Curriculum & Analytics Mentorship Faculty
Composed of experienced data professionals focusing on practical SQL, Power BI, Excel, and Python education for Indian analytics aspirants.