Counting rows with different conditions in one query

Question

Let's say I have this table:

 id | status | type
----+--------+----------
  1 | new    | car
  2 | new    | boat
  3 | used   | car
  4 | new    | car

and I wanted to count all the new vehicles, and the number of cars in one go, how to do that?

I tried this:

SELECT COUNT(status='new'), COUNT(type='car') FROM table;

but it always counts to 4 (the total amount of rows). The only thing I can think of is using a CASE inside the COUNT, but is there a cleaner way?

Lukasz Szozda · Accepted Answer · 2019-01-14 16:28:42Z

1

You could use FILTER to perform conditional aggregation:

SELECT COUNT(*) FILTER(WHERE status='new'), COUNT(*) FILTER(WHERE type='car')
FROM tab;

Alternatively SUM:

SELECT SUM((status='new')::int), SUM((type='car')::int) FROM table;

answered Jan 14, 2019 at 16:28

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1