From: | Janning Vygen <vygen(at)gmx(dot)de> |
---|---|
To: | Roberto Fichera <kernel(at)tekno-soft(dot)it> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query aid |
Date: | 2004-12-16 11:21:15 |
Message-ID: | 200412161221.16018.vygen@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 2004-12-16 11:30:14 | Re: question about index |
Previous Message | Andreas Kretschmer | 2004-12-16 11:01:39 | Re: [despammed] question about index |