Re: COUNT() BASED ON MULTIPLE WHERE CONDITIONS - Mailing list pgsql-novice

On May 17, 2009, at 9:05 , ...tharas wrote:

> Required Result set*
>
>        Year  W_count  T_count  S_Count
>        2007       0            1           0
>        2008       3            1           1
>        2009       0            0           1
>
> I could write three separate queries for this like
>
>        select count(PersonId) as W_count from my_table where Year =
> 2008
>        and workstatus = 'W'
>
> can I get the whole result in a single query?

SELECT "year",
        SUM(CAST((workstatus = 'W')  AS INT)) AS w_count
        SUM(CAST((workstatus = 'T')  AS INT)) AS t_count
        SUM(CAST((workstatus = 'S')  AS INT)) AS s_count
   FROM my_table
   GROUP BY "year"

Michael Glaesemann
grzm seespotcode net