FIltering SQL column by number of occurrences

Question

This is a follow-up to a question already asked and answered here.

Basically, for a table like this

Ford
Ford
Ford
Honda
Chevy
Honda
Honda
Chevy

If an output with the number of occurrences is desired, like so:

Ford   3
Honda  3
Chevy  2

The query is:

select car_made, count(*) from cars
group by car_made

Now, what I want is for the output to show only those values where the count is greater than 2. So, desired output:

Ford   3
Honda  3

How do I write the query for that?

I tried

select car_made, count(*) as carcount 
from cars
where carcount>2
group by car_made

But that doesn't seem to work.

@Reborn: `Group by` already assumes `distinct` unless `select all` is specified. But how does that help? — Snowman, Mar 26 '17 at 09:35

score 5 · Accepted Answer · answered Mar 26 '17 at 09:35

5

You need to use HAVING clause.

    select car_made, count(*) as carcount 
     from cars
    group by car_made
   having count(*) > 2

answered Mar 26 '17 at 09:35

TheName

This is right, any filter you want to do over a group by needs to be done with having instead of where. – Rick van Lieshout Mar 26 '17 at 09:37

1 Answers1