Postgresql Multiple counts for one table

Question

From two columns in my table I want to get a unified count for the values in these columns. As an example, two columns are:

Table: reports

|   type        |   place   |  
 ----------------------------------------- 
|   one         |   home    |  
|   two         |   school  |  
|   three       |   work    |  
|   four        |   cafe    |  
|   five        |   friends |  
|   six         |   mall    |  
|   one         |   work    |  
|   one         |   work    |  
|   three       |   work    |  
|   two         |   cafe    |  
|   five        |   cafe    |  
|   one         |   home    |

If I do: SELECT type, count(*) from reports group by type

I get:

|   type        |   count   |  
-----------------------------  
|   one         |   4       |  
|   two         |   2       |  
|   three       |   2       |  
|   four        |   1       |  
|   five        |   2       |  
|   six         |   1       |

Im trying to get something like this: (one rightmost column with my types grouped together and multiple columns with the count vales for each place) I get:

|   type        |   home    |   school  |   work    |   cafe    |   friends |   mall    |  
-----------------------------------------------------------------------------------------  
|   one         |   2       |           |   2       |           |           |           |  
|   two         |           |   1       |           |   1       |           |           |  
|   three       |           |           |   2       |           |           |           |  
|   four        |           |           |           |   1       |           |           |  
|   five        |           |           |           |   1       |   1       |           |  
|   six         |           |           |           |           |           |   1       |

which would be the result of running a count like the one above for every place like this:

SELECT type, count(*) from reports where place  = 'home'
group by type
SELECT type, count(*) from reports where place  = 'school'
group by type
SELECT type, count(*) from reports where place  = 'work'
group by type
SELECT type, count(*) from reports where place  = 'cafe'
group by type
SELECT type, count(*) from reports where place  = 'friends'
group by type
SELECT type, count(*) from reports where place  = 'mall'
group by type

Is this possible with postgresql?

Thanks in advance.

Here we have some definitive lookup right. eg( 'home','school' ...) i have the situtation lookup may know at runtime only. any template sql for that? — Pyare
– Pyare, Commented Sep 5, 2013 at 7:11

pratik garg · Accepted Answer · 2017-12-14 07:57:31Z

80

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

edited Dec 14, 2017 at 7:57

answered Jun 12, 2013 at 22:37

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

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

4 Comments

Craig Ringer Over a year ago

To get blank (null) columns instead of zero, wrap the sums in nullif calls. Using count with null instead of 0 (by omitting the ELSE 0 entirely) for the failed case works fine too, you don't have to change to sum.

mosid Over a year ago

The last comma in the sql statement (right before the "from" keyword) causes an error.

S T Mohammed Over a year ago

how to get records for where home,school,work,cafe,friends,mall values >0

pratik garg Over a year ago

apologies for this late reply. By doing small change in query you can get output as what you want.

crmpicco · Accepted Answer · 2020-05-04 08:29:44Z

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 mall
FROM reports
GROUP 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 mall
FROM reports
GROUP BY type

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

esq0 · Accepted Answer · 2017-07-13 14:57:51Z

25

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 mall
FROM
  reports
GROUP BY 
  type

answered Jul 13, 2017 at 14:57

esq0

2513 silver badges3 bronze badges

Comments

Mohamed Aymen Charrada · Accepted Answer · 2022-04-24 08:35:40Z

10

You can use filter and count like this:

select type,
       count(1) FILTER (where place = 'home') as home,
       count(1) FILTER (where place = 'school') as school,
       count(1) FILTER (where place = 'work') as work,
       count(1) FILTER (where place = 'cafe') as cafe,
       count(1) FILTER (where place = 'friends') as friends,
       count(1) FILTER (where place = 'mall') as mall
from reports
group by type

answered Apr 24, 2022 at 8:35

Mohamed Aymen Charrada

4355 silver badges13 bronze badges

1 Comment

Sheharyar Over a year ago

This should get more upvotes! FILTER works beautifully with aggregates!

Collectives™ on Stack Overflow

Postgresql Multiple counts for one table

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related