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@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 -- David Fetter <david@xxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
