Request a Demo
Home   >   mailing lists

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

From PALAYRET Jacques
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
Msg-id 964199985.345244682.1772035211240.JavaMail.zimbra@meteo.fr
Whole thread Raw
In response to PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query  (PALAYRET Jacques <jacques.palayret@meteo.fr>)
List pgsql-general
Tree view
hello again,

It seems that following can be a solution (adding sub-query " SELECT * FROM " after the main FROM clause)  :
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(c1),
  (SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1)
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
;

Regards
De: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
À: pgsql-general@lists.postgresql.org
Envoyé: Mercredi 25 Février 2026 12:14:28
Objet: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR:  subquery uses ungrouped column from outer query

Hello,

I have 3 tables : t1, t2 and t3
t1(posx integer, posy integer, dat timestamp, val_grid numeric)
  with Primary Key=(posx, posy, dat)
t2(id integer, x integer, y integer)
  with Primary Key=(id)
t3(id integer, dat timestamp, c1 numeric)
  with Primary Key=(id, dat)

My (very simplified) query  that doesn't work:
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(c1),
  (SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1)
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)
--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
;
ERROR:  subquery uses ungrouped column "t2.id" from outer query

-> The problem is on the third line (the subquery) :
(SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1)

However, If I replace "FULL OUTER" by "LEFT OUTER" or by "RIGHT OUTER", it works.

Could you please tell me how to correct the query, to fix this problem?

Regards
----- Météo-France -----
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Next
From: "David G. Johnston"
Date:
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query