K455306
K455306

Reputation: 119

HAVING COUNT(*) with MAX() sub query

I am trying to build a statement that will show the first_name, last_name, course_no, description for the instructor that teaches the most java courses. My max statement shows the maximum number of courses however, when I executed the complete statement then I got no results. Below is the Query:

SELECT first_name, last_name, course_no, description
FROM(
    SELECT i.first_name, i.last_name, c.course_no, c.description
    FROM instructor i INNER JOIN section s
    ON i.instructor_id = s.instructor_id
    JOIN course c ON c.course_no = s.course_no
    WHERE c.description LIKE '%Java%') 
GROUP BY first_name, last_name, course_no, description
HAVING COUNT(*) =
    (SELECT MAX(course_count)
     FROM (SELECT DISTINCT first_name, last_name, COUNT(*) As course_count
           FROM(
               SELECT i.first_name, i.last_name, c.course_no, c.description
               FROM instructor i INNER JOIN section s
               ON i.instructor_id = s.instructor_id
               JOIN course c ON c.course_no = s.course_no
               WHERE c.description LIKE '%Java%')
           GROUP BY first_name, last_name));

Upvotes: 0

Views: 404

Answers (1)

ruakh
ruakh

Reputation: 183241

You have a mismatch between your outer query and the subquery in your HAVING clause: the former has GROUP BY first_name, last_name, course_no, description, the latter only GROUP BY first_name, last_name. Naturally, you won't generally find any 4-tuples of the former columns with as many rows as the most common pair of the latter columns.

Upvotes: 1

Related Questions