Re: complex query

On Sat, Oct 27, 2012 at 6:01 PM, Mark Fenbers <mark.fenbers@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.