Why am I getting an ORA-00937 error?

Question

This is my oracle query:

SELECT      DISTINCT Student.student_id, student_name, SUM(credit)
FROM        Student, Class, Enrolls
WHERE       Student.student_id = Enrolls.student_id
AND         Class.schedule_num = Enrolls.schedule_num
AND         Class.semester = Enrolls.semester
AND         grade != 'F'
AND         grade IS NOT null
ORDER BY    student_id ASC;

The query worked fine until I added SUM(credit). After that I get:

ORA-00937: not a single-group group function

Please help, thanks.

score 6 · Accepted Answer · answered Oct 29 '13 at 02:46

It appears you are missing the GROUP BY clause in your SQL statement. It should look something like:

SELECT      Student.student_id, student_name, SUM(credit)
FROM        Student, Class, Enrolls
WHERE       Student.student_id = Enrolls.student_id
AND         Class.schedule_num = Enrolls.schedule_num
AND         Class.semester = Enrolls.semester
AND         grade != 'F'
AND         grade IS NOT null
GROUP BY    Student.student_id, student_name
ORDER BY    student_id ASC;

The GROUP BY would also make the use of DISTINCT unnecessary. — Dijkgraaf, Oct 29 '13 at 02:48

Dba · Answer 2 · 2013-10-29T03:01:49.137

Try like this,

SELECT      DISTINCT s.student_id, student_name, SUM(credit)
FROM        Student s, 
            CLASS c, 
            Enrolls e
WHERE       s.student_id = e.student_id
AND         c.schedule_num = e.schedule_num
AND         c.semester = e.semester
AND         grade != 'F'
AND         grade IS NOT NULL
GROUP BY    s.student_id, student_name
ORDER BY    student_id ASC;

You have missed GROUP BY clause in you query, If you want the sum the credit, you need to group by student_id & student_name.

Why am I getting an ORA-00937 error?

2 Answers2