George Pavlov <gpavlov@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx
> [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jeff
> Lanzarotta
> Sent: Tuesday, September 11, 2007 8:51 AM
> To: PostgreSQL GENERAL List
> Subject: 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
