On Sat, Oct 27, 2012 at 6:01 PM, Mark Fenbers <mark.fenb...@noaa.gov> wrote: > I have a query: > SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true GROUP > BY id; > > This gives me 3 columns, but what I want is 5 columns where the next two > columns -- SUM(col3), SUM(col4) -- have a slightly different WHERE clause, > i.e., WHERE condition2 = true. > > I know that I can do this in the following way: > SELECT id, SUM(col1), SUM(col2), (SELECT SUM(col3) FROM mytable WHERE > condition2 = true), (SELECT SUM(col4) FROM mytable WHERE condition2 = true) > FROM mytable WHERE condition1 = true GROUP BY id; > > Now this doesn't seem to bad, but the truth is that condition1 and > condition2 are both rather lengthy and complicated and my table is rather > large, and since embedded SELECTs can only return 1 column, I have to repeat > the exact query in the next SELECT (except for using "col4" instead of > "col3"). I could use UNION to simplify, except that UNION will return 2 > rows, and the code that receives my resultset is only expecting 1 row. > > Is there a better way to go about this?
I'd do somethings like: select * from ( select id, sum(col1), sum(col2) from tablename group by yada ) as a [full, left, right, outer] join ( select id, sum(col3), sum(col4) from tablename group by bada ) as b on (a.id=b.id); and choose the join type as appropriate. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql