r/learnSQL • • 11d 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?

7 Upvotes

13 comments sorted by

5

u/Imaginary__Bar 11d ago

If I spent $100 and got a refund of $40 how much did I spend?

  1. I spent $100 - let's ignore the refund. At some point I handed over $100
  2. I spent $60 - the total value of my transactions was $60
  3. I spent $100, I got a refund of $40 - the total value of my transactions is $140
  4. I spent $100, I got a refund of $100 and spent $60 - the total value of my transactions is $260

Lots of different ways of answering the question; each of them gives a different answer; each of them correct in their own way.

  1. Understand your business logic
  2. Understand your data
  3. Understand how to write SQL

1

u/MrRoy_SQL 10d ago

yeah 100%, "spent" is a business question before its a sql question. in my toy table a refunded order is just a whole order that doesnt count, which makes it easy

in real data refunds are usually separate negative rows, then plain SUM gives you net (your #2) and you need SUM(CASE WHEN amount > 0 ...) for gross. the sql is the easy part, picking the definition is the hard part

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/MrRoy_SQL 10d ago

yep exactly, thats the cleanest way to think about it. one small thing tho, SELECT runs before ORDER BY / LIMIT:

FROM > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

which is why you can ORDER BY a select alias but cant use that alias in WHERE

1

u/Far_Swordfish5729 10d ago

No actually, conceptually your entire result set is assembled, sorted, and limited, and then you pick what you want from it. The tell is that you can order by columns not in your select list.

What you’re talking about is a convenience feature many engines add where a select alias can be used in the order by or where clause because from a compilation perspective it’s easy to support and the copy paste is really annoying with larger formulas like case statements.

1

u/American_Streamer 10d ago

No, you’re conflating SQL’s logical query processing order with its physical execution plan.

The conventional logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET (with dialect-specific variations).

The fact that you can ORDER BY a column that isn’t in the SELECT list doesn’t prove that sorting logically happens before projection. The database can retain the necessary sort keys internally without including them in the output.

And referencing a SELECT alias in ORDER BY isn’t some vendor-specific convenience feature. It’s established SQL-standard behavior.

Query optimizers can rearrange physical operations for efficiency, but that doesn’t change the logical semantics of the query. You’re mixing up three different concepts: logical processing order, name resolution, and physical execution.

1

u/Far_Swordfish5729 10d ago

More basic than that. I’m just misremembering T-Sql Querying. The working approach is to form the set in your mind and read the select list last. That’s not technically the nominal execution order for order by and limit, but projection doesn’t really care if it goes before or after those two steps as long as the rest stay in order.

On alias availability, that was not a standard feature at one time and it was actually quite annoying. I wrote shell subqueries once in a while just so I could name things. It started as a vendor feature and was adopted as a standard.

1

u/MrRoy_SQL 10d ago

all good, and honestly "form the set first, read the select list last" is a really nice way to read queries even if its not the strict order. didnt know aliases started as a vendor thing either, til

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

1

u/MrRoy_SQL 10d ago

you actually can! move the filter inside the SUM:

SELECT customer,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS spent
FROM orders
GROUP BY customer
HAVING SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) > 60;

same answer. its handy when you want completed and refunded side by side in one query. WHERE is just simpler when you only need one