Date: Wed, 25 Feb 2026 16:42:03 +0000 (GMT)
From: PALAYRET Jacques <jacques.palayret@meteo.fr>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Message-ID: <676773466.345278228.1772037723135.JavaMail.zimbra@meteo.fr>
In-Reply-To: <52acd108-2949-4762-80c2-c761662fde73@aklaver.com>
References: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr> <52acd108-2949-4762-80c2-c761662fde73@aklaver.com>
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column
 of GROUP BY expression ERROR: subquery uses ungrouped column from outer
 query
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Thread-Index: omUYDMRFSeNpNgrrqztg8bf8f7/0yw==
Archived-At: <https://www.postgresql.org/message-id/676773466.345278228.1772037723135.JavaMail.zimbra%40meteo.fr>
Precedence: bulk

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=3Did LIMIT 1)
, count(*)
FROM (
SELECT * FROM (
  (SELECT * FROM t1 ) AS t1
  JOIN t2  ON (t1.posx=3Dt2.x AND t1.posy=3Dt2.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.klaver@aklaver.com>
=C3=80: "PALAYRET Jacques" <jacques.palayret@meteo.fr>, pgsql-general@lists=
.postgresql.org
Envoy=C3=A9: Mercredi 25 F=C3=A9vrier 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 que=
ry

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?
=09


--=20
Adrian Klaver
adrian.klaver@aklaver.com