Hey Reader!

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 and confusion sets in. "Why can't I filter by the sum?"

It seems like it SHOULD work, but the truth is that 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

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

This kind of information tells marketing where to focus their budget.

Now, let's look at difficulty levels that have more than $100k total revenue (here's where HAVING comes in)

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
HAVING SUM(p.amount) > 100000
ORDER BY total_revenue DESC;

WHERE:

• Filters individual rows BEFORE grouping
• Removes incomplete payments and cancelled trips
• Happens first in SQL's execution order

HAVING:

• Filters grouped results AFTER aggregation
• Only shows groups meeting certain conditions
• Happens after GROUP BY

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%

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

The SQL Order of Execution

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

P.S. What else do you want to know about SQL? Let me know in this 2-minute survey.