I keep getting the error "Group function is not allowed here" but no group function used,-CodePudding

I am trying to run this query but I keep getting an error when it get to the count function saying "Group function not allowed".

select student.student_id,student.first_name,student.last_name, enrollment.enroll_date 
 from student
 left outer join Enrollment 
 on student.student_id = enrollment.student_id 
 left join Section on section.section_id = enrollment.section_id 
 left join course on section.course_no = course.course_no 
 where count(course.course_no) = 0
 and enrollment.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy');

CodePudding user response：

Can you please try below query?

select student.student_id,student.first_name,student.last_name, enrollment.enroll_date 
 from student
 left outer join Enrollment 
 on student.student_id = enrollment.student_id 
 left join Section on section.section_id = enrollment.section_id 
 left join course on section.course_no = course.course_no 
 where course.course_no is null
 and enrollment.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy');

CodePudding user response：

COUNT can't be used in WHERE clause anyway; you should move it to HAVING, but then you also need a GROUP BY. Also, get used to use table aliases and try to format it; make code easier to read. Something like this:

select s.student_id, s.first_name, s.last_name, e.enroll_date 
from student s left join enrollment e on s.student_id = e.student_id 
               left join section t    on t.section_id = e.section_id 
               left join course c     on t.course_no  = c.course_no 
where e.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy')
group by s.student_id, s.first_name, s.last_name, e.enroll_date 
having count(c.course_no) = 0;

To illustrate it (as I don't have your tables), have a look at the following example based on Scott's EMP table:

This is what you tried:

SQL> select job
  2  from emp
  3  where count(comm) = 0;
where count(comm) = 0
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

As I said, count can't be used in where clause. So rewrite it to:

SQL> select job
  2  from emp
  3  group by job
  4  having count(comm) = 0;

JOB
---------
CLERK
PRESIDENT
MANAGER
ANALYST

SQL>

Now it works.