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,

roman · Accepted Answer · 2013-10-12 06:53:02Z

7

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

answered Oct 12, 2013 at 6:53

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

user2695222 Over a year ago

I threw an order by type at the end and it is exactly what I want. Thank you.

John Woo · Accepted Answer · 2013-10-12 06:52:55Z

1

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

answered Oct 12, 2013 at 6:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

user2695222 Over a year ago

Appreciate the help. Thank you

Collectives™ on Stack Overflow

PostgreSQL multiple count() where conditions in a single query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related