CODEWITHSUNDEEP
postgresqlcount
Bart Friederichs
Bart Friederichs

Reputation: 33501

Counting rows with different conditions in one query

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?

Upvotes: 0

Views: 82

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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;

Upvotes: 1

Related Questions