WHERE or HAVING to return an aggregation by customer

I'm looking for the maximum purchase amount order in the range 50$ and 200$ by combination of each customer and order date.

For 2 different queries I don't have the result set, so I don't understand the difference between WHERE and HAVING to filter.

Query 1: WHERE clause

SELECT id, order_date, MAX(price)
FROM orders
WHERE price BETWEEN 50 AND 200
GROUP BY id;

Query 2: HAVING clause

SELECT id, order_date, MAX(price)
FROM orders
GROUP BY id
HAVING MAX(price) BETWEEN 50 AND 200

Result for Query 1:

id  order_date  MAX(price)
1   2013-01-10  103.50
5   2013-01-01  195.50
6   2013-01-01  172.50
8   2013-01-01  147.20
9   2013-01-01  165.60
10  2013-01-01  184.00
12  2013-01-18  191.40
24  2013-01-02  138.00
25  2013-01-07  100.63
26  2013-01-16  86.71
30  2013-01-03  155.25
35  2013-01-08  96.60
36  2013-01-02  169.74
40  2013-01-03  197.80
45  2013-01-02  124.20
48  2013-01-02  149.04
50  2013-01-03  157.55
52  2013-01-03  173.42
60  2013-01-03  186.30
75  2013-01-02  181.13
100 2013-01-04  147.20
125 2013-01-04  184.00

Result for Query 2:

id  order_date  MAX(price)
25  2013-01-03  100.63
26  2013-01-16  86.71
35  2013-01-08  96.60
45  2013-01-02  124.20
52  2013-01-03  173.42

It seems that it's the result of the HAVING clause query that is expected and I don't understand why or even what the differences are in between the 2 queries.

CodePudding user response：

Because the WHERE clause limits the selected rows to those in the specified range, the MAX(price) will definitely be in that range because the WHERE excludes all rows whose price is < 50 or > 200.

The HAVING clause, however, is applied after the MAX is selected. If a row has a MAX(price) either < 50 or > 200 it is excluded by the HAVING clause.

Which one is right I don't know, because I don't know your requirement. You'll have to decide that.

Added later:

If you have one customer with 4 transactions on the same day at prices 10, 100, 150, and 500 then: WHERE price BETWEEN 50 AND 200 will pick out the 100 and 150 transactions. The GROUP BY will pick out the 150 and drop the 100.

Similarly, without the WHERE the query will pick all 4 transactions, and then the GROUP BY will pick the 500 and drop the other 3. Finally the HAVING will drop the 500 and have nothing left to show.

Put in a few day's worth of data shaped like that and then play with various WHERE's and HAVING's, and see what happens when you have both. You need to play about with this stuff to get used to it.

And I still can't tell you what you want when you have 4 prices as I've used. You have to decide what you want and then write the query to get it.