Search code examples
sqloracle-databaseselectsubquerycorrelated-subquery

SQL subselect group function not allowed

I'm facing a problem with an SQL subquery: I need to write a query which returns the courses where the number of subscriptions (count(employeeNumber)) is greater than the maximum allowed number of subscriptions (Maximum).

In my original query I'm getting following error: Group function is not allowed here.

The query:

SELECT c.CourseName 
FROM courses c 
INNER JOIN subscriptions s ON s.courseCode = c.CourseCode 
INNER JOIN plannedCourses p ON p.CourseCode = s.CourseCode
WHERE COUNT(s.EmployeeNumber) > (SELECT maximum 
                                 FROM plannedCourses 
                                 WHERE s.CourseCode = p.CourseCode);

The table layout:

Table layout

How can i achieve the desire result?

Thanks in advance!!

Solution

  • You could rewrite your query as follows:

    select c.coursename
  from courses c
  join subscriptions s
    on (s.coursecode = c.coursecode)
  join PlannedCourses p
    on (p.coursecode = c.coursecode)
  group by c.coursename 
         , p.maximum
  having count(s.Employeenumber) > p.maximum