Re: Get multiple columns with counts from one table. - Mailing list pgsql-general

On Jun 13, 2013, at 24:09, chuydb <jdelbosque@cic.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.