From: Roberto Fichera <kernel@tekno-soft.it>
To: Janning Vygen <vygen@gmx.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query aid
Date: Thu, 16 Dec 2004 12:41:22 +0100
Message-ID: <6.2.0.14.2.20041216124028.0593d8b0@mail.tekno-soft.it> (raw)
In-Reply-To: <200412161221.16018.vygen@gmx.de>
References: <6.2.0.14.2.20041216110510.07500100@mail.isolaweb.it>
<200412161221.16018.vygen@gmx.de>
At 12.21 16/12/2004, you wrote:
>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;
Many thanks! This works well :-)!
>kind regards,
>janning
Roberto Fichera.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kernel@tekno-soft.it, vygen@gmx.de
Subject: Re: Query aid
In-Reply-To: <6.2.0.14.2.20041216124028.0593d8b0@mail.tekno-soft.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox