pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery aid
3+ messages / 2 participants
[nested] [flat]
* Query aid
@ 2004-12-16 10:34 Roberto Fichera <kernel@tekno-soft.it>
2004-12-16 11:21 ` Re: Query aid Janning Vygen <vygen@gmx.de>
0 siblings, 1 reply; 3+ messages in thread
From: Roberto Fichera @ 2004-12-16 10:34 UTC (permalink / raw)
To: pgsql-sql
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
Thanks in advance.
Roberto Fichera.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query aid
2004-12-16 10:34 Query aid Roberto Fichera <kernel@tekno-soft.it>
@ 2004-12-16 11:21 ` Janning Vygen <vygen@gmx.de>
2004-12-16 11:41 ` Re: Query aid Roberto Fichera <kernel@tekno-soft.it>
0 siblings, 1 reply; 3+ messages in thread
From: Janning Vygen @ 2004-12-16 11:21 UTC (permalink / raw)
To: Roberto Fichera <kernel@tekno-soft.it>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query aid
2004-12-16 10:34 Query aid Roberto Fichera <kernel@tekno-soft.it>
2004-12-16 11:21 ` Re: Query aid Janning Vygen <vygen@gmx.de>
@ 2004-12-16 11:41 ` Roberto Fichera <kernel@tekno-soft.it>
0 siblings, 0 replies; 3+ messages in thread
From: Roberto Fichera @ 2004-12-16 11:41 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-16 11:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-16 10:34 Query aid Roberto Fichera <kernel@tekno-soft.it>
2004-12-16 11:21 ` Janning Vygen <vygen@gmx.de>
2004-12-16 11:41 ` Roberto Fichera <kernel@tekno-soft.it>
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