From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Select statement with except clause
Date: Fri, 24 May 2013 11:15:48 -0700 (PDT)
Message-ID: <1369419348510-5756790.post@n5.nabble.com> (raw)
In-Reply-To: <CAAY=A7-rk7WYa=Gbo-9iHn5Ngo2AQhuhr5jQpK5-pw4ZuOcHeQ@mail.gmail.com>
References: <CAAY=A79+vsQN3zzPC7CtSoJFPxbp-Kf=yxg-hdCd6AEb3i6P5Q@mail.gmail.com>
<1369334190307-5756661.post@n5.nabble.com>
<CAAY=A7-rk7WYa=Gbo-9iHn5Ngo2AQhuhr5jQpK5-pw4ZuOcHeQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Reply-all is acceptable; but standard list protocol is to respond at the end
of the message after performing "quote editing".
JORGE MALDONADO wrote
> Firstly, I want to thank you for responding.
> Secondly, I wonder if I should only reply to the mailing list (I clicked
> Reply All); if this is the case, I apologize for any inconvenience. Please
> let me know so I reply correctly next time.
>
> I will describe my issue with more detail. I need to perform 2 very
> similar
> queries as follows:
>
> *** QUERY 1 ***
> SELECT fldA, fldB, fldC, SUM(fldD) AS fldD
> FROM tableA
> WHERE condition1
> GROUP BY fldA, fldB, fldC
>
> *** QUERY 2 ***
> SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD
> FROM tableA
> WHERE condition2
> GROUP BY fldA, fldB, fldC
>
> As you can see, both reference the same table and the same fields.
>
> The differences between the queries are:
> a) The last SELECTED field is multiplied by (-1) in the second query.
> b) The WHERE conditions.
>
> What I finally need is to exclude records generated by QUERY1 from QUERY2
> when fldA, fldB and fldC are equal in both results.
Example query layout; not promising it is the most efficient but it works.
WITH
q1 AS ( SELECT fldA, fldB, fldC, sum(fldD) AS sumD ... )
, q2 AS ( SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sumD ... WHERE ...
AND (fldA, fldB, fldC) NOT IN (SELECT (q1.fldA, q2.fldB, q3.fldC) FROM q1)
)
SELECT fldA, fldB, fldC, sumD FROM q1
UNION ALL
SELECT fldA, fldB, fldC, sumD FROM q2
;
If you actually explain the goal and not just ask a technical question you
might find that people suggest alternatives that you are not even
considering.
SELECT fldA, fldB, fldC, sum_positive, sum_negative
FROM (SELECT fldA, fldB, fldC, sum(fldD) AS sum_positive) q1
NATURAL FULL OUTER JOIN (SELECT fldA, fldB, fldC, sum(fldD)*-1 AS
sum_negative) q2
WHERE <...>
Food for thought.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756790.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: polobo@yahoo.com
Subject: Re: Select statement with except clause
In-Reply-To: <1369419348510-5756790.post@n5.nabble.com>
* 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