public inbox for [email protected]  

help / color / mirror / Atom feed

From: PALAYRET Jacques <[email protected]>
To: [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:00:11 +0000 (GMT)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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" <[email protected]> 
À: [email protected] 
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 
[email protected] 
Fixe : +33 561078319 

view thread (6+ messages)  latest in thread

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]
  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