Limit (Cut-off) number returned by function 'count(*)' in MySQL

Question

When I try this in MySQL:

select make,count(*) as num from TABLE where num > 100 group by make

I got:

Error Code: 1054, SQL State: 42S22]  Unknown column 'num' in 'where clause'

Is there a way to limit count(*)?

I'd say HAVING is for aggregate function conditions. Do a derived table instead, put condition outside. — jarlh, Sep 05 '17 at 18:13
`select make,count(*) as num from TABLE GROUP BY make HAVING count(*) > 100` — Rubens Farias, Sep 05 '17 at 18:18

score 0 · Answer 1 · answered Sep 05 '17 at 18:14

0

You can't put column aliasas in the WHERE clause. Use a derived table instead:

select make, num
from
(
    select make,count(*) as num from TABLE  group by make
) dt
where num > 100

answered Sep 05 '17 at 18:14

jarlh

1 Answers1