pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect statement with except clause
4+ messages / 2 participants
[nested] [flat]
* Select statement with except clause
@ 2013-05-23 18:10 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: JORGE MALDONADO @ 2013-05-23 18:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select statement with except clause
@ 2013-05-23 18:36 David Johnston <polobo@yahoo.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2013-05-23 18:36 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select statement with except clause
@ 2013-05-24 17:26 JORGE MALDONADO <jorgemal1960@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: JORGE MALDONADO @ 2013-05-24 17:26 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql
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.
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?
> > 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.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select statement with except clause
@ 2013-05-24 18:15 David Johnston <polobo@yahoo.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2013-05-24 18:15 UTC (permalink / raw)
To: 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-05-24 18:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-23 18:10 Select statement with except clause JORGE MALDONADO <jorgemal1960@gmail.com>
2013-05-23 18:36 ` David Johnston <polobo@yahoo.com>
2013-05-24 17:26 ` JORGE MALDONADO <jorgemal1960@gmail.com>
2013-05-24 18:15 ` David Johnston <polobo@yahoo.com>
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