using an aggregate function to narrow my query

Question

I want to do something like this:

SELECT locations.id, places.id, scannables.id, SUM(scannables.available)
FROM `scannables` 
INNER JOIN places ON scannables.place_id = places.id
INNER JOIN locations ON places.location_id = locations.id
WHERE locations.id = 2474 AND scannables.bookdate 
BETWEEN '2009-08-27' and date_add('2009-08-27', INTERVAL 3 DAY)

AND SUM(scannables.available) >= 3

GROUP BY scannables.place_id

This works... except for the "AND SUM(scannables.available) >= 3"

I'm not sure how to get it to work,

Answer 1

In MySQL, I don't think you can perform a WHERE on an aggregate's alias. I think you must use HAVING, which filters the results after the query is performed.

Why would using WHERE SUM(...) not work?

Answer 2

Ok, so this seems to work:

SELECT locations.id, hostels.id, scannables.id, SUM(scannables.available)
FROM `scannables` 
INNER JOIN hostels ON scannables.hostel_id = hostels.id
INNER JOIN locations ON hostels.location_id = locations.id

WHERE locations.id = 2474 AND scannables.bookdate BETWEEN '2009-08-27' and date_add('2009-08-27', INTERVAL 3 DAY)

GROUP BY scannables.hostel_id
HAVING SUM(scannables.available) > 3

The HAVING following the GROUP

Thanks!