SQL: Putting condition on result of an aggregate function

Question

this thing works fine:

SELECT c.id, c.name, c.ascii_name, COUNT(*) AS nr
    FROM cities c 
    INNER JOIN jobs j ON (j.city_id = c.id ) 
    WHERE j.is_active = 1 
    GROUP BY c.name
limit 100

but when i wanna put condition on new column nr it says column not found

SELECT c.id, c.name, c.ascii_name, COUNT(*) AS nr
    FROM cities c 
    INNER JOIN jobs j ON (j.city_id = c.id ) 
    WHERE j.is_active = 1 and nr > 100
    GROUP BY c.name
limit 100

Answer 1

You should put the condition on nr in the HAVING clause, like this:

SELECT c.id, c.name, c.ascii_name, COUNT(*) AS nr
    FROM cities c 
    INNER JOIN jobs j ON (j.city_id = c.id ) 
    WHERE j.is_active = 1
    GROUP BY c.name
    HAVING nr > 100
limit 100

This is because nr is the result of an aggregate function (COUNT(*)) and as such is not available at the time the WHERE filter is applied.

EDIT: in some database servers, the reference to nr doesn't work; you can also use HAVING COUNT(*) > 100.