Date: Fri, 24 May 2013 11:15:48 -0700 (PDT)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1369419348510-5756790.post@n5.nabble.com>
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>
Subject: Re: Select statement with except clause
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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