CODEWITHSUNDEEP
sqlmysqlruby-on-railsaggregatesum
holden
holden

Reputation: 13591

using an aggregate function to narrow my query

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,

Upvotes: 0

Views: 157

Answers (2)

holden
holden

Reputation: 13591

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!

Upvotes: 0

Jeff Ober
Jeff Ober

Reputation: 5027

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?

Upvotes: 2

Related Questions