The WHERE vs HAVING Mistake That’s Sabotaging Your SQL Queries

Hello ,

Here’s a mistake I see all the time:

An analyst needs to find which expedition difficulty levels generate the most revenue. They write this:

SELECT 
    difficulty_level,
    SUM(revenue) AS total_revenue
FROM expeditions
WHERE SUM(revenue) > 50000  -- ❌ ERROR
GROUP BY difficulty_level;

The query fails. They’re confused. “Why can’t I filter by the sum?”

Many analysts get tripped up on this. The answer: WHERE filters rows. HAVING filters groups.

Let me show you the difference using real data.

The Right Way: WHERE vs HAVING

I just ran this query against the live Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):

-- Revenue by expedition difficulty
SELECT 
    e.difficulty_level,
    COUNT(DISTINCT ei.instance_id) AS total_trips,
    COUNT(DISTINCT b.booking_id) AS total_bookings,
    SUM(p.amount) AS total_revenue,
    ROUND(AVG(p.amount), 2) AS avg_payment_amount
FROM expeditions e
    INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
    INNER JOIN bookings b ON ei.instance_id = b.instance_id
    INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
    AND ei.status = 'completed'
GROUP BY e.difficulty_level
ORDER BY total_revenue DESC;

Here’s what we found for Summit Adventures:

Advanced trips: 24 trips, 51 bookings, over $121,000 revenue
Beginner trips: 28 trips, 71 bookings, over $121,000 revenue
Intermediate trips: 16 trips, 43 bookings, over $71,000 revenue
Expert trips: 19 trips, 39 bookings, over $64,000 revenue

The insight: Advanced and Beginner trips generate nearly identical revenue (both around $121K), but Advanced trips have 43% higher average payments ($1,782 vs $1,345).

That tells marketing exactly where to focus.

The Critical Difference

WHERE (Line 11-12):
Filters individual rows BEFORE grouping
Removes incomplete payments and cancelled trips
Happens first in SQL’s execution order

HAVING (not in this query, but here’s how you’d use it):
Filters grouped results AFTER aggregation
Only shows groups meeting certain conditions
Happens after GROUP BY

If we wanted to show only difficulty levels with revenue over $100K, we’d add:

HAVING SUM(p.amount) > 100000

That would filter out Intermediate and Expert (both under $100K).

When To Use Each

Use WHERE when filtering individual records:
Show only completed payments
Exclude cancelled bookings
Filter by date ranges
Remove test data

Use HAVING when filtering aggregated results:
Show customers who spent more than $5,000 total
Find products with more than 100 orders
Display regions with average sales above $50K
Identify campaigns with ROI over 200%

Quick test: If your filter uses SUM(), COUNT(), AVG(), MAX(), or MIN(), you need HAVING. If it doesn’t, you need WHERE.

The Execution Order (This Changes Everything)

SQL executes in this order:
1. FROM/JOIN — Get the tables
2. WHERE — Filter individual rows
3. GROUP BY — Create groups
4. HAVING — Filter groups
5. SELECT — Calculate aggregates
6. ORDER BY — Sort results

That’s why you can’t use SUM() in WHERE — the grouping hasn’t happened yet!

Try This At Your Job

Next time you need to analyze grouped data, ask yourself:

“Am I filtering before or after grouping?”

Before (individual rows) → WHERE
After (grouped results) → HAVING

Common business scenarios:
WHERE: Show sales from Q4 only → filters date before grouping
HAVING: Show salespeople with $100K+ in total sales → filters sum after grouping

Understanding this one concept will help you avoid most GROUP BY frustrations.

Real-World Application

Let’s say your CMO asks: “Which marketing channels brought in customers who spent more than $10,000 total?”

Wrong approach (common mistake):

WHERE total_spent > 10000  -- Error: total_spent doesn't exist yet

Right approach (what works):

GROUP BY marketing_channel
HAVING SUM(amount) > 10000  -- Filters after calculating totals

The difference? You deliver the answer in minutes instead of hours of debugging.

Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. This is exactly the kind of pattern I teach in SQL for Business Impact — practical queries that solve real business problems. The course launch just wrapped up, but if you’re interested in joining the next cohort, you can get on the waitlist at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What’s your biggest SQL frustration right now? Hit reply and tell me — I might cover it in a future email.