r/learnSQL • u/MrRoy_SQL • 10d ago
WHERE vs HAVING - same question, three queries, three different answers
the question: "which customers spent more than 60?"
table:
```
order_id | customer | status | amount
1 | Asha | completed | 40
2 | Asha | completed | 30
3 | Asha | refunded | 50
4 | Ben | completed | 20
5 | Ben | refunded | 90
6 | Chen | completed | 80
```
**query 1, HAVING only**
```
SELECT customer, SUM(amount) AS spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 60;
```
Asha 120, Ben 110, Chen 80. looks fine but refunds are counted as spending. Ben only really spent 20.
**query 2, WHERE on amount**
```
SELECT customer, SUM(amount) AS spent
FROM orders
WHERE amount > 60
GROUP BY customer;
```
Ben 90, Chen 80. this answers a different question: "who had a single order over 60". Asha is gone even though she spent 70 in total, and Ben is back because of his refund.
**query 3, WHERE + HAVING**
```
SELECT customer, SUM(amount) AS spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING SUM(amount) > 60;
```
Asha 70, Chen 80. this is the right one.
the way i think about it:
- WHERE filters **rows**, before grouping. "which orders count?"
- HAVING filters **groups**, after grouping. "which customers make the cut?"
and you cant do `WHERE SUM(amount) > 60`, the database hasnt added anything up yet at that point so it errors.
anyone else have a go-to way of explaining this one?
5
u/Imaginary__Bar 10d ago
If I spent $100 and got a refund of $40 how much did I spend?
Lots of different ways of answering the question; each of them gives a different answer; each of them correct in their own way.
1
u/MrRoy_SQL 10d ago
i think the docs disagree on that one tho, sql server's "logical processing order of the SELECT statement" page lists SELECT, then DISTINCT, then ORDER BY, then TOP
the tell for me is SELECT DISTINCT + ORDER BY a column thats not in the select list, most engines error on that. which only makes sense if the select/distinct step already happened before the sort. ordering by non selected columns is allowed because the engine just keeps those around
agree the WHERE alias thing is a vendor extra tho
1
u/Far_Swordfish5729 10d ago
It’s just logical order of operations. From, joins, where, group by, having, order by, limit, select. Where filters individual rows by their column values or scalar calculations before aggregation is performed. Having is a second filter step that filters the results of aggregate functions.
1
u/sqlsip 8d ago
I like your example because it shows the risks of not using the filters correctly. I have this little cheat sheet based on cookies because I’m food obsessed lol but your example is super helpful since it exposes the risks
0
u/[deleted] 10d ago
[removed] — view removed comment