From: Mark Fenbers <mark.fenbers@noaa.gov>
To: pgsql-sql@postgresql.org
Subject: complex query
Date: Sat, 27 Oct 2012 20:01:21 -0400
Message-ID: <508C75D1.2070906@noaa.gov> (raw)
This is a multi-part message in MIME format.
--------------030503080902050804050401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFCC" text="#000000">
I have a query:<br>
SELECT id, SUM(col1), SUM(col2) FROM mytable WHERE condition1 = true
GROUP BY id;<br>
<br>
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.<br>
<br>
I know that I can do this in the following way:<br>
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;<br>
<br>
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.<br>
<br>
Is there a better way to go about this?<br>
<br>
Thanks for any help you provide.<br>
Mark<br>
<br>
</body>
</html>
--------------030503080902050804050401
Content-Type: text/x-vcard; charset=utf-8;
name="mark_fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="mark_fenbers.vcf"
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
--------------030503080902050804050401--
Attachments:
[text/x-vcard] mark_fenbers.vcf (345B, ../508C75D1.2070906@noaa.gov/2-mark_fenbers.vcf)
download | inline:
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:Ohio River Forecast Center;Hydrometeorological Analysis & Support Unit
adr:1901 South OH-134;;National Weather Service;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Senior Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
url:weather.gov/ohrfc
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark.fenbers@noaa.gov
Subject: Re: complex query
In-Reply-To: <508C75D1.2070906@noaa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox