current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

share|improve this question
    
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 Sep 5 '13 at 7:11

2 Answers 2

up vote 7 down vote accepted

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

share|improve this answer
    
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. –  Craig Ringer Jun 12 '13 at 23:09
    
Thanks for your answers guys, this helped me solve my issue. –  Chuydb Jun 25 '13 at 1:27
    
The last comma in the sql statement (right before the "from" keyword) causes an error. –  mosid May 6 at 12:09
    
thanks @mosid .. –  pratik garg May 8 at 10:26
up vote 3 down vote

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 casted to 1 when condition is met.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.