Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query

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" <adrian(dot)klaver(at)aklaver(dot)com>
À: "PALAYRET Jacques" <jacques(dot)palayret(at)meteo(dot)fr>, pgsql-general(at)lists(dot)postgresql(dot)org
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
adrian(dot)klaver(at)aklaver(dot)com