From: Janning Vygen <vygen@gmx.de>
To: Roberto Fichera <kernel@tekno-soft.it>
Subject: Re: Query aid
Date: Thu, 16 Dec 2004 12:21:15 +0100
User-Agent: KMail/1.7.1
Cc: pgsql-sql@postgresql.org
References: <6.2.0.14.2.20041216110510.07500100@mail.isolaweb.it>
In-Reply-To: <6.2.0.14.2.20041216110510.07500100@mail.isolaweb.it>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200412161221.16018.vygen@gmx.de>

Am Donnerstag, 16. Dezember 2004 11:34 schrieb Roberto Fichera:
> Hi all,
>
> I have a table acct as (username, terminatedate, terminatecause)
> I would like to build a query which returns three columns orderd by data
> like:
>
> date_trunc( 'day', terminatedate ) | count(cause1) | count(cause2)
>
> where cause1/2 are two type of termination cause from the field
> terminatecause.
>
> for example acct table could be:
>
> user1|01/01/2004 01:01:01| error
> user2|01/01/2004 01:02:01| error
> user1|01/01/2004 02:00:01| normal
> user3|02/01/2004 10:00:01| normal
> user2|02/01/2004 10:10:01| error
>
> I would like to obtain:
>
> date          |normal| error
> 01/01/2004|      1  |   2
> 02/01/2004|      1  |   1

try something like this:

SELECT 
  date_trunc( 'day', terminatedate ) AS day, 
  SUM(
    CASE 
      WHEN cause = 'error' 
      THEN 1
      ELSE 0
    END
  ) AS error_count,
  SUM(
    CASE 
      WHEN cause = 'normal' 
      THEN 1
      ELSE 0
    END
  ) AS normal_count,
 
FROM acct AS acct1
GROUP BY day
ORDER BY day ASC;

kind regards,
janning