Date: Wed, 30 Apr 2025 12:29:11 +0000 (GMT)
From: PALAYRET Jacques <jacques.palayret@meteo.fr>
To: pgsql-general@lists.postgresql.org
Message-ID: <557588747.121401914.1746016151124.JavaMail.zimbra@meteo.fr>
Subject: SQL ERROR subquery uses ungrouped column  in PostgreSQL
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="=_0486f3e5-e9f2-4db7-9141-00854e3605ae"
Thread-Index: EDyJM6+/8SaEAMixJw/1cRgDSUK0Xg==
Thread-Topic: SQL ERROR subquery uses ungrouped column in PostgreSQL
Archived-At: <https://www.postgresql.org/message-id/557588747.121401914.1746016151124.JavaMail.zimbra%40meteo.fr>
Precedence: bulk

--=_0486f3e5-e9f2-4db7-9141-00854e3605ae
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hello, 

I have an unexpected error in my following query (of course, the query has been simplified here to request help): 

SELECT num_poste, to_char(dat, 'YYYYMM')::integer dat, CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint AS num_decade 
, CASE 
WHEN ( CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint IN (1 , 2) AND count(glot)=10 ) 
THEN 
( 
SELECT valeur 
FROM table3 
WHERE num_serie_coef=CASE WHEN num_poste <96000000 THEN 0 WHEN num_poste BETWEEN 97100000 and 98899999 THEN num_poste/100000 END 
AND coef = 'APRIME_ANGSTROM' 
AND num_mois=substr( to_char(dat,'yyyymm') , 5, 2)::smallint 
) 
ELSE NULL 
END AS toto 
FROM table1 JOIN table2 USING (num_poste) 
GROUP BY num_poste, 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 
ORDER BY num_poste, 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 "table1.dat" from outer query 
LIGNE 10 : AND num_mois=substr( to_char(dat,'yyyymm') , 5, 2)::smallint 
^ 

=> In the subquery, the semantic analysis of the query considers the column " dat " instead of the expression " to_char(dat,'yyyymm') ", which is actually a grouped column. 
Is this normal? How can I simply resolve the problem? 

Sincerely. 

--=_0486f3e5-e9f2-4db7-9141-00854e3605ae
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: garamond, new york, times, serif; fo=
nt-size: 12pt; color: #000000"><div>Hello,<br><br>I have an unexpected erro=
r in my following query (of course, the query has been simplified here to r=
equest help):<br><br>SELECT num_poste, to_char(dat, 'YYYYMM')::integer dat,=
 CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 =
THEN 2 ELSE 3 END::smallint AS num_decade<br>, CASE<br>WHEN (&nbsp; CASE FL=
OOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 E=
LSE 3 END::smallint IN (1 , 2) AND count(glot)=3D10&nbsp; )<br>THEN <br>(<b=
r>SELECT valeur<br>FROM table3<br>WHERE num_serie_coef=3DCASE WHEN num_post=
e &lt;96000000 THEN 0 WHEN num_poste BETWEEN 97100000 and 98899999 THEN num=
_poste/100000 END<br>AND coef =3D 'APRIME_ANGSTROM' <br>AND num_mois=3Dsubs=
tr(<strong>to_char(dat,'yyyymm')</strong>, 5, 2)::smallint<br>)<br>ELSE NUL=
L<br>END AS toto<br>FROM&nbsp; table1&nbsp;&nbsp; JOIN&nbsp;&nbsp; table2&n=
bsp;&nbsp; USING (num_poste)<br><strong>GROUP BY</strong> num_poste, <stron=
g>to_char(dat, 'YYYYMM')</strong>, CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),=
'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint<br>ORDER BY n=
um_poste, 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<br><br><span s=
tyle=3D"font-family: courier new, courier, monaco, monospace, sans-serif;" =
data-mce-style=3D"font-family: courier new, courier, monaco, monospace, san=
s-serif;"><strong>ERROR</strong>:&nbsp; <strong><span style=3D"background-c=
olor: rgb(255, 0, 0);" data-mce-style=3D"background-color: #ff0000;">subque=
ry uses ungrouped column</span> "table1.dat" from outer query</strong></spa=
n><br><span style=3D"font-family: courier new, courier, monaco, monospace, =
sans-serif;" data-mce-style=3D"font-family: courier new, courier, monaco, m=
onospace, sans-serif;">LIGNE 10 : AND num_mois=3Dsubstr(<strong>to_char(dat=
,'yyyymm')</strong>, 5, 2)::smallint</span><br><span style=3D"font-family: =
courier new, courier, monaco, monospace, sans-serif;" data-mce-style=3D"fon=
t-family: courier new, courier, monaco, monospace, sans-serif;">&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ^</span=
><br><br>=3D&gt; In the subquery, the semantic analysis of the query consid=
ers the column " <em>dat</em> " instead of the expression " <em>to_char(dat=
,'yyyymm')</em> ", which is actually a grouped column.<br>Is this normal? H=
ow can I simply resolve the problem?<br><br>Sincerely.</div></div></body></=
html>
--=_0486f3e5-e9f2-4db7-9141-00854e3605ae--