public inbox for [email protected]
help / color / mirror / Atom feedFrom: PALAYRET Jacques <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Date: Wed, 25 Feb 2026 16:42:03 +0000 (GMT)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Answers :
Note that, of course, the queries I provide are greatly simplified to give the ideas.
1) (SELECT * FROM t1 ) ---> It means the real SQL query for t1 comes from a subquery
2) Why are the field names not table qualified e.g. t2.id, t3.id, etc? ---> because of the USING
3) An exemple, with sum(COALESCE(c1, val_grid)) in the select_list :
TABLE t1 ;
posx | posy | dat | val_grid
------+------+---------------------+----------
1 | 2 | 2026-01-01 00:00:00 | 123
2 | 1 | 2026-01-01 00:00:00 | 21
2 | 1 | 2026-01-03 00:00:00 | 21
(3 lignes)
TABLE t2 ;
id | x | y
----+---+---
21 | 2 | 1
12 | 1 | 2
(2 lignes)
TABLE t3 ;
id | dat | c1 | c2
----+---------------------+-----+----
12 | 2026-01-01 00:00:00 | 129 | 0
21 | 2026-01-01 00:00:00 | 219 | 0
21 | 2026-01-21 00:00:00 | 211 | 0
21 | 2026-01-09 00:00:00 | 211 | 0
(4 lignes)
SELECT
id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint,
avg(val_grid), sum(COALESCE(c1, val_grid)),
(SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1)
, count(*)
FROM (
SELECT * FROM (
(SELECT * FROM t1 ) AS t1
JOIN t2 ON (t1.posx=t2.x AND t1.posy=t2.y)
FULL OUTER JOIN (SELECT * FROM t3 ) t3 USING(id, dat)
) tb
) t
--WHERE ...
GROUP BY id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint
;
id | to_char | int2 | avg | sum | rr1 | count
----+---------+------+----------------------+-----+-----+-------
21 | 202601 | 3 | | 211 | | 1
21 | 202601 | 1 | 21.0000000000000000 | 451 | | 3
12 | 202601 | 1 | 123.0000000000000000 | 129 | | 1
(3 lignes)
Regards
----- Mail original -----
De: "Adrian Klaver" <[email protected]>
À: "PALAYRET Jacques" <[email protected]>, [email protected]
Envoyé: Mercredi 25 Février 2026 17:00:03
Objet: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Questions:
1) Why FROM (SELECT * FROM t1 ) AS t1 instead of just FROM t1?
Same for JOIN (SELECT * FROM t3 ) t3
2) Why are the field names not table qualified e.g. t2.id, t3.id, etc?
3) What is the desired outcome?
--
Adrian Klaver
[email protected]
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox