I have the following statement in SQL Developer and when I want to count the number of products per customer, it won't let me because when grouping them I get the following
ORA-00937: group function is not a single-group 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at line: 1699, column: 20
And line 1699 is this:
UPPER(TO_CHAR(CLI.NUMRUN,'09G999G999') || '-' || DVRUN) AS "RUN CLIENT",
SELECT
TO_CHAR(SYSDATE - 1,'YYYY') AS "AÑO TRIBUTARIO",
UPPER(TO_CHAR(CLI.NUMRUN,'09G999G999') || '-' || DVRUN) AS "RUN CLIENTE",
INITCAP(CLI.PNOMBRE) || ' ' || SUBSTR(CLI.SNOMBRE,1,1) || '. ' || INITCAP(CLI.APPATERNO) || ' ' || INITCAP(CLI.APMATERNO) AS "NOMBRE CLIENTE",
COUNT(PIC.NRO_SOLIC_PROD) AS "TOTAL INV"
FROM
CLIENTE CLI JOIN PRODUCTO_INVERSION_CLIENTE PIC
ON(CLI.NRO_CLIENTE = PIC.NRO_CLIENTE)
WHERE
PIC.COD_PROD_INV IN (30, 35, 40, 45, 50, 55);
GROUP BY
"AÑO TRIBUTARIO",
"RUN CLIENTE",
"NOMBRE CLIENTE";
Column "aliases" are one of the last things that the SQL interpreter solves, it is not the last, because it is
ORDER BY
(aliases usually work in this statement). In your case, what happens is that for the engine, at least at the time of processing theGROUP BY
does not exist"AÑO TRIBUTARIO"
, nor the other aliases, so ultimately you are grouping by any string, not the column, so the error says functionCOUNT()
with ungrouped columns is invalid. There are two alternatives you can try:Repeat the columns in the
GROUP BY
Use a subquery
This way you materialize the column names before
GROUP BY
Note: Conceptually similar to a subquery, you have the COMMON TABLE EXPRESSION