Where to use WHERE clause in this query?

I want only results WHERE COUNT = 0, but every way I tried making this query it failed. Please help!

SELECT 
    username, 
    COUNT(user_id) AS COUNT 
FROM 
    users 
LEFT JOIN 
    photos ON users.id = photos.user_id 
GROUP BY 
    username 
ORDER BY 
    COUNT ASC;

Answer

Use HAVING. For example:

SELECT username, COUNT(user_id) AS COUNT 
FROM users 
LEFT JOIN photos ON users.id = photos.user_id 
GROUP BY username 
HAVING COUNT = 0
ORDER BY COUNT ASC;

HAVING filters “resulting rows” after they are aggregated. WHERE, on the other side, filters rows before they are aggregated.

Or… you can use an anti-join:

select u.username from users u
left join photos p on u.id = p.user_id 
where p.user_id is null