[SQL] complex query

[Mark Fenbers]

  
  
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?

Thanks for any help you provide.
Mark

  

<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] complex query

[Scott Marlowe]

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

Re: [SQL] complex query

[Mark Fenbers]

  
  

  
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

  

<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] complex query

[Scott Marlowe]

On Sat, Oct 27, 2012 at 7:56 PM, Mark Fenbers  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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql