Обсуждение: Select statement with except clause
I have one SELECT statement as follows:
SELECT
lpt_titulo as tmt_titulo,
tmd_nombre as tmt_nombre,
tmd_album as tmt_album,
SUM(lpt_puntos) AS tmt_puntos
FROM listas_pre_titulos
INNER JOIN cat_tit_media ON lpt_titulo = tmd_clave "
WHERE condition
The above statement must have an EXCEPT clause which includes another SELECT statement almost identical, the difference is in the WHERE condition and also in one of the fields; "SUM(lpt_puntos) AS tmt_puntos" should be "SUM(lpt_puntos) * -1 AS tmt_puntos". I only need to convert such a field to a negative value.
How does the EXCEPT work? Do fields should be identical?
I need the difference to be on the first 3 fields.
Respectfully,
Jorge Maldonado
JORGE MALDONADO wrote > How does the EXCEPT work? Do fields should be identical? > I need the difference to be on the first 3 fields. Except operates over the entire tuple so yes all fields are evaluated and, if they all match, the row from the "left/upper" query is excluded. If you need something different you can use some variation of: IN EXISTS NOT IN NOT EXISTS with a sub-query (correlated or uncorrelated as your need dictates). For example: SELECT col1, col2, col3, sum(col4) FROM tbl WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not correlated GROUP BY col1, col2, col3 SELECT col1, col2, col3, sum(col4) FROM tbl WHERE NOT EXISTS ( SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table if it matches the outer reference (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3) ) -- correlated; reference "tbl" within the query inside the where clause GROUP BY col1, col2, col3 I do not follow your example enough to provide a more explicit example/solution but this should at least help point you in the right direction. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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.
*** 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.
With respect,
Jorge Maldonado
On Thu, May 23, 2013 at 1:36 PM, David Johnston <polobo@yahoo.com> wrote:
JORGE MALDONADO wrote> How does the EXCEPT work? Do fields should be identical?Except operates over the entire tuple so yes all fields are evaluated and,
> I need the difference to be on the first 3 fields.
if they all match, the row from the "left/upper" query is excluded.
If you need something different you can use some variation of:
IN
EXISTS
NOT IN
NOT EXISTS
with a sub-query (correlated or uncorrelated as your need dictates).
For example:
SELECT col1, col2, col3, sum(col4)
FROM tbl
WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not
correlated
GROUP BY col1, col2, col3
SELECT col1, col2, col3, sum(col4)
FROM tbl
WHERE NOT EXISTS (
SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table
if it matches the outer reference
(tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3)
) -- correlated; reference "tbl" within the query inside the where clause
GROUP BY col1, col2, col3
I do not follow your example enough to provide a more explicit
example/solution but this should at least help point you in the right
direction.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.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-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.