Count() using Where clause and Left Join

Question

I have a query similar to the following:

SELECT users.id FROM users LEFT JOIN sales ON installations.customer = users.id

What I would like to say is something like "WHERE count(sales.id) > 4" - meaning that if the user has more than 4 sales assoc with them. I am not sure if I am going about this the wrong way or not though

Answer 1

select
    users.id

from users

join sales on /* your join condition here */

group by users.id

having count(sales.id) > 4

This will group all of the sales by user, then return only those sales that have more than four records in the sales table.

I didn't duplicate your join condition from above because it didn't seem to make much sense, as it's referencing tables that aren't in your query anywhere.

Answer 2

I think you want something similar to this

select users.id, count(sales.id)
from users
LEFT JOIN sales ON installations.customer = users.id
group by users.id
having count(Sales.id) > 5

Answer 3

You need to use "group by" and "having" clause.

Try

SELECT users.id FROM users LEFT JOIN sales ON installations.customer = users.id GROUP BY users.id HAVING count(sale.id) > 4

See here for more GROUPBY on W2C