Re: Get multiple columns with counts from one table.

On Jun 13, 2013, at 24:09, chuydb <jdelbosque(at)cic(dot)mx> wrote:

> Hi,
> 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 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 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???

You can do that like this:

SELECT type, SUM(CASE place WHEN 'home' THEN 1 ELSE 0 END),
SUM(CASE place WHEN 'school' THEN 1 ELSE 0 END),
etc.
FROM reports
WHERE place IN ('home', 'school', etc.)
GROUP BY type

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.