Skip site navigation (1) Skip section navigation (2)

COUNT() BASED ON MULTIPLE WHERE CONDITIONS

From: "(dot)(dot)(dot)tharas" <tharasp(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: COUNT() BASED ON MULTIPLE WHERE CONDITIONS
Date: 2009-05-17 13:05:56
Message-ID: 82d9696a0905170605r301fa515w7c13a074f1ed40ee@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Dallas MorisetteDate: 2009-05-17 14:12:55
Subject: Re: Include result of function call within WHERE clause in results
Previous:From: Dallas MorisetteDate: 2009-05-17 00:44:42
Subject: Include result of function call within WHERE clause in results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group