Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query aid

Query aid

From
Roberto Fichera
Date:
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.

Re: Query aid

From
Janning Vygen
Date:
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

Re: Query aid

From
Roberto Fichera
Date:
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.