Re: Question about a query with two count fields

On Tue, Sep 11, 2007 at 08:55:53AM -0700, George Pavlov wrote:
> select
> to_char(ts, 'MM/DD/YYYY') as "day",
> str,
> proc,
> sum(case when z!=0 then 1 end) as good,

This case statement returns true when z factorial is zero, so I'd
recommend the SQL standard <> or IS NOT DISTINCT FROM instead.

> sum(case when z =0 then 1 end) as bad
> from foobar
> where str != 99999

This may parse differently, but <> is more cautious.

Cheers,
David.

> 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

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate