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
