Ошибка групповая функция не является одногруппной

Table of contents
• Error code 937, SQL state 42000: ORA-00937: not a single-group group function
• SQL Error [937] [42000]: ORA-00937: not a single-group group function
• ORA-00937: not a single-group group function Solution
• ORA-00937: not a single-group group function
• Oracle / PLSQL: ORA-00937 Error Message

Error code 937, SQL state 42000: ORA-00937: not a single-group group function

A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

SELECT 
    DP.CODE_VALEUR CODE,
    MAX(VA.CODE_TYPE_VALEUR) CODE_TYPE_VALEUR,
    MAX(VA.NOM_VALEUR) STOCK_NAME,
    (SUM(COURS_ACQ_VALEUR) / SUM(QUANTITE_VALEUR)) CMP,
    MAX(DP.CODE_COMPTE) CODE_COMPTE,
    SUM(DP.QUANTITE_VALEUR) QTEVALEUR,
    round(SUM(DP.VALORISATION_BOURSIERE), 3) VALORISATION_BOURSIERE,
    round((SUM(DP.VALORISATION_BOURSIERE) / SUM(DP.QUANTITE_VALEUR)),
            3) COURS
FROM
    DETAILPORTEFEUILLE DP,
    VALEUR VA
WHERE
    DP.CODE_COMPTE IN (SELECT 
            P.CODE_COMPTE_RATTACHE
        FROM
            PROCURATION P
        WHERE
            P.IDWEB_MASTER = 8
                AND NVL(P.CAN_SEE_PORTEFEUILLE, 0) != 0)
        AND VA.CODE_VALEUR = DP.CODE_VALEUR
        AND DP.QUANTITE_VALEUR > 0
        AND DP.CODE_VALEUR = 'TN0007250012'

GROUP BY DP.CODE_VALEUR

GROUP BY DP.CODE_VALEUR

SQL Error [937] [42000]: ORA-00937: not a single-group group function

Teams. Q&A for work. Connect and share knowledge within a single location that is structured and easy to search. Learn more

WITH BOXCOUNT AS (                                                         
SELECT 
PRODUCT_CODE 

 , ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX
 
 , CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
       WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
    END AS SPAREBOX

FROM 
LOG0104M 

GROUP BY 
    PRODUCT_CODE)

SELECT
L04.PRODUCT_CODE
, L04.PRODUCT_NAME 
, L04.TOTAL_QUANTITY
, L04.PACKING_STYLE

, BC.FULLBOX
, BC.SPAREBOX

, SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX

    
FROM LOG0104M L04
JOIN BOXCOUNT BC
ON   BC.PRODUCT_CODE = L04.PRODUCT_CODE 

GROUP BY  
L04.PRODUCT_CODE
, L04.PRODUCT_NAME
, L04.TOTAL_QUANTITY
, L04.PACKING_STYLE

, BC.FULLBOX
, BC.SPAREBOX

SQL Error [937] [42000]: ORA-00937: not a single-group group function

CASE WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
       WHEN SUM((TOTAL_QUANTITY - ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
    END AS SPAREBOX

sum(CASE WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) = 0 THEN 0
       WHEN ((TOTAL_QUANTITY - ROUND((TOTAL_QUANTITY/PACKING_STYLE))) * PACKING_STYLE) > 0 THEN 1
    END) AS SPAREBOX

ORA-00937: not a single-group group function Solution

Solution 1: Add the expressions into the GROUP BY clause. ORA-00937: not a single-group group function 00937. 00000 — «not a single-group group …

SELECT department_id, SUM(salary)
FROM employee;

ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

SELECT department_id, SUM(salary)
FROM employee
GROUP BY department_id;

SELECT department_id, SUM(salary)
FROM employee;

ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

SELECT SUM(salary)
FROM employee;

SELECT department_id, SUM(salary)
FROM employee;

ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

SELECT department_id, salary
FROM employee;

SELECT department_id
FROM employee;

ORA-00937: not a single-group group function

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance …

ORA-00937: not a single-group group function

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history;
select sample_id,max(SAMPLE_TIME) from v$active_session_history
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history;
select sample_id,max(SAMPLE_TIME) from v$active_session_history
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> select sample_id,max(SAMPLE_TIME) from v$active_session_history group by sample_id;

SQL> select max(SAMPLE_TIME) from v$active_session_history;

MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
14-MAY-20 04.57.59.450 PM

SQL>

ORA-00937: not a single-group group function

Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual …

select s.student_id, s.first_name, 
s.last_name, count(o.order_id) No
from students s, orders o 
where s.student_id = o.student_id
order by s.student_id;

select s.student_id, s.first_name, 
s.last_name, count(o.order_id) No
from students s, orders o 
where s.student_id = o.student_id
group by s.student_id, s.first_name, 
s.last_name, o.order_id
order by s.student_id;

Oracle / PLSQL: ORA-00937 Error Message

Rewrite the SELECT statement so that the column or expression listed in the SELECT list is also found in the GROUP BY clause. Option #2. Remove the GROUP BY function (ie: MIN …

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees;

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

Next Lesson PHP Tutorial