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]
