Thread: COUNT() BASED ON MULTIPLE WHERE CONDITIONS

Dear All,

I want to write a query which returns three different counts based on three different conditions. The following data set and result set
may explain my scenario.

Table Data      
        PersonId     work status          Year
               1               W                  2008
               2               W                  2008
               3               T                    2008
               4               S                   2008
               5               W                  2008
               6               T                    2007
               7               S                   2009

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?

Thanks all.
roshni

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