COUNT in a WHERE statement SQL

Question

My Table consists of 3 columns; id, state, carColour, and over 1mil rows. I want to return all states which have more black cars than white cars.

I have tried:

SELECT state
FROM table
WHERE state IN (SELECT state
                FROM table
                WHERE COUNT(carColour = 'Black') > COUNT(carColour = 'White'))
GROUP BY state

But obviously a count() in a where statement doesnt work... How can I solve this?

What type of database is it? SQL server? Oracle? – Nick Sep 25 '20 at 10:07 — Nick, Sep 25 '20 at 10:07

score 3 · Accepted Answer · answered Sep 25 '20 at 10:07

3

No need for a subquery. You can filter in the having clause, using conditional aggreation:

select state
from mytable
group by state
having sum(case when carColour = 'Black' then 1 else 0 end) 
     > sum(case when carColour = 'White' then 1 else 0 end)

This uses standard case syntax, which is supported pretty much everywhere. Some databases have shortcuts. For example, Postgres and SQLite support the filter clause:

having count(*) filter(where carColour = 'Black') 
     > count(*) filter(where carColour = 'White')

answered Sep 25 '20 at 10:07

GMB

216,147
25
84
135

first one works. Filter seems to not be working even tho Im using sqlite. Thanks! – broccoli Sep 25 '20 at 10:19
@broccoli: you need SQLite 3.30.0 for the `filter` clause... – GMB Sep 25 '20 at 12:38

COUNT in a WHERE statement SQL

1 Answers1