complex query

From: Mark Fenbers <mark(dot)fenbers(at)noaa(dot)gov>
To: pgsql-sql(at)postgresql(dot)org
Subject: complex query
Date: 2012-10-28 00:01:21
Message-ID: 508C75D1.2070906@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Attachment Content-Type Size
unknown_filename text/html 1.3 KB
mark_fenbers.vcf text/x-vcard 347 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Scott Marlowe 2012-10-28 00:24:32 Re: complex query
Previous Message Gary Stainburn 2012-10-26 09:24:39 Re: pull in most recent record in a view