Re: Select statement with except clause

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.