SQL: setting conditions on the result of an aggregate function

Question

this thing works great:

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 want to put a condition on a new column nr, it says that the column was 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 must put the condition in nr in the HAVING clause, for example:

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 due to the fact that nr is the result of an aggregate function (COUNT (*)) and, as such, is not available when the WHERE filter is applied.

EDIT: on some database servers, the link to nr does not work; you can also use HAVING COUNT(*) > 100.

Answer 2

you must put your expression to read

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 COUNT(*) > 100
limit 100

Answer 3

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