Postgresql Multiple counts for one table

you can use case in this case -

SELECT type,        sum(case when place  = 'home' then 1 else 0 end) as Home,       sum(case when  place  = 'school' then 1 else 0 end) as school,       sum(case when  place  = 'work' then 1 else 0 end) as work,       sum(case when  place  = 'cafe' then 1 else 0 end) as cafe,       sum(case when  place  = 'friends' then 1 else 0 end) as friends,       sum(case when  place  = 'mall' then 1 else 0 end) as mall  from reports group by type

It should solve your problem

@S T Mohammed,To get such type we can simply use using after group or where condition in outer query, as below -

select type, Home, school, work, cafe, friends, mall from (SELECT type,        sum(case when place  = 'home' then 1 else 0 end) as Home,       sum(case when  place  = 'school' then 1 else 0 end) as school,       sum(case when  place  = 'work' then 1 else 0 end) as work,       sum(case when  place  = 'cafe' then 1 else 0 end) as cafe,       sum(case when  place  = 'friends' then 1 else 0 end) as friends,       sum(case when  place  = 'mall' then 1 else 0 end) as mall  from reports group by type ) where home >0 and School >0 and Work >0 and cafe>0 and friends>0 and mall>0

Answer by praktik garg is correct, it is not necessary to use else 0:

SELECT type,        sum(case when place  = 'home' then 1 end) as home,       sum(case when  place  = 'school' then 1 end) as school,       sum(case when  place  = 'work' then 1 end) as work,       sum(case when  place  = 'cafe' then 1 end) as cafe,       sum(case when  place  = 'friends' then 1 end) as friends,       sum(case when  place  = 'mall' then 1 end) as mallFROM reportsGROUP BY type

You can also use the following even shorter syntax:

SELECT type,        sum((place  = 'home')::int) as home,       sum((place  = 'school')::int) as school,       sum((place  = 'work' )::int) as work,       sum((place  = 'cafe' )::int) as cafe,       sum((place  = 'friends')::int) as friends,       sum((place  = 'mall')::int) as mallFROM reportsGROUP BY type

This will work because boolean true is cast to 1 when condition is met.

You can use filter clause as well:

SELECT  type,  sum(1) FILTER (WHERE place = 'home') AS home,  sum(1) FILTER (WHERE place = 'school') AS school,  sum(1) FILTER (WHERE place = 'work') AS work,  sum(1) FILTER (WHERE place = 'cafe') AS cafe,  sum(1) FILTER (WHERE place = 'friends') AS friends,  sum(1) FILTER (WHERE place = 'mall') AS mallFROM  reportsGROUP BY   type