Re: complex query

On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers <mark(dot)fenbers(at)noaa(dot)gov> wrote:
> 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.
>
> Thanks! Your idea worked like a champ!
> Mark

The basic rules for mushing together data sets is to join them to put
the pieces of data into the same row (horiztonally extending the set)
and use unions to pile the rows one on top of the other.

One of the best things about PostgreSQL is that it's very efficient at
making these kinds of queries efficient and fast. I've written 5 or 6
page multi-join multi-union queries that still ran in hundreds of
milliseconds, returning thousands of rows.