MYSQL COUNT and only show results above X

Question

Hey All

Is it possible to count all reservations for users and only get results where users have had more than X reservations. Here is my code to just count all user's reservations

SELECT 
    u.firstname, u.lastname u.email, COUNT(r.reservation_id) as orders
FROM 
    users AS u LEFT OUTER JOIN
    reservation AS r USING (user_id)
GROUP BY
    u.id

Can I add to this query to say WHERE orders >= 5 ??

Hope you can advise

Lee

Answer 1

Hi,

take a look at the HAVING option that should let you do what you want.

Answer 2

not sure if mysql has this: but in Oracle,

HAVING COUNT(*) > 5 

is available

Answer 3

You want to use 'HAVING' not 'WHERE' when you're acting on aggregation:

SELECT 
    u.firstname, u.lastname u.email, COUNT(r.reservation_id) as orders
FROM 
    users AS u LEFT OUTER JOIN
    reservation AS r USING (user_id)
GROUP BY
    u.id
HAVING orders >= 5;

(I think mysql allows you to column aliases in the HAVING clause ... postgres doesn't, so you'd have to use HAVING COUNT(r.reservation_id) >= 5)