PostgreSQL multiple count() where conditions in a single query

Question

I normally execute the following SQL queries in PostgreSQL 9.1 sequentially via psycopg2 every couple of seconds:

select count(type) from bag where type= 'fruit';
select count(type) from bag where type= 'vegtable';
select count(type) from bag where type= 'other';
select count(type) from bag where type= 'misc';

Is it possible to do the same thing in a single select query such that I get a count for each type even if that count is zero. The following would work if it gave me the zero counts when there are zero for a given type.

 select type, count(*) from bag group by type;

Thank you,

Answer 1

Use derived table as anchor of the query:

select a.type, count(b.type) 
from (values ('fruit'), ('vegtable'), ('other'), ('misc')) as a(type)
    left outer join bag as b on b.type = a.type
group by a.type

sql fiddle demo

Answer 2

There can be many possible solutions for this. One is by generating all desired type in a subquery using UNION ALL and do a LEFT JOIN against bag table. In this case, all the types that you want to get will be shown on the result list and the non-existing type on table bag will have zero count. This will almost work on all RDBMS.

SELECT  a.type,
        COUNT(b.type) TotalCount
FROM
        (
            SELECT 'fruit' AS type UNION ALL
            SELECT 'vegtable' AS type UNION ALL
            SELECT 'other' AS type UNION ALL
            SELECT 'misc' AS type 
        ) AS a
        LEFT JOIN bag AS b
            ON a.type = b.type
GROUP   By a.type

• SQLFiddle Demo