From: | Jeff Lanzarotta <delux256-postgresql(at)yahoo(dot)com> |
---|---|
To: | PostgreSQL GENERAL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question about a query with two count fields |
Date: | 2007-09-11 16:57:52 |
Message-ID: | 787260.61602.qm@web36810.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Awesome, thanks...
George Pavlov <gpavlov(at)mynewplace(dot)com> wrote: select
to_char(ts, 'MM/DD/YYYY') as "day",
str,
proc,
sum(case when z!=0 then 1 end) as good,
sum(case when z =0 then 1 end) as bad
from foobar
where str != 99999
group by 1,2,3
order by 1
;
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jeff
> Lanzarotta
> Sent: Tuesday, September 11, 2007 8:51 AM
> To: PostgreSQL GENERAL List
> Subject: [GENERAL] Question about a query with two count fields
>
> Hello,
>
> I am in need of producing a query that has two count fields
> in it... Something like:
>
> select to_char(ts, 'MM/DD/YYYY') as "day", str, proc,
> (select count (*) as good from foobar where z != 0),
> (select count (*) as bad from foobar where z = 0)
> from foobar
> where str != 99999
> group by str, day, proc order by str
>
> From this query, the output should look something like this:
>
> day | str | proc | good | bad
> 09/10/2007 | 1 | xyz | 1 | 3
> 09/10/2007 | 1 | abc | 3 | 2
> 09/10/2007 | 2 | xyz | 3 | 5
> 09/10/2007 | 2 | abc | 1 | 2
>
> I hope I have explained the situation...
>
> I appreciate the help...
>
> Thanks.
>
>
> -Jeff
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
From | Date | Subject | |
---|---|---|---|
Next Message | Phoenix Kiula | 2007-09-11 17:02:13 | Re: Hardware recommendation: which is best |
Previous Message | sharmi Joe | 2007-09-11 16:54:54 | oracle rank() over partition by queries |