Re: Question about a query with two count fields

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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.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
>