ORA-00937: not a single-group group function

ORA-00937: not a single-group group function

Oracle PL/SQL error message: 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 column expression is included in a GROUP BY clause.

Solution:

Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.




Students table

STUDENT_IDFIRST_NAMELAST_NAME
1DanielSCOTT
2AnthonySIMMONS
3SophiaTHOMPSON
4EmilyPETERSON
5DavidDAWSON
6GabrielLEWIS

Orders table

ORDER_IDCOURSE_IDSTUDENT_ID
116
345
2156
2214

Example:

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;

Output:

ORA-00937: not a single-group group function

Correct:

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;

Output:

STUDENT_IDFIRST_NAMELAST_NAMENO
4EmilyPETERSON1
5DavidDAWSON1
6GabrielLEWIS1
6GabrielLEWIS1