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
