jpuriol jpuriol - 6 months ago 7
SQL Question

Getting min from GROUP BY clause

I have the following table

course


course(idcourse, title, description, price, date, instructor)


And I want to get the instructor with less courses. So far I can get each instructor with the number of courses he teaches

SELECT instructor, count(*)
FROM courses
GROUP BY instructor;


The result of this query in my example is this one:

INSTRUCTOR COUNT(*)
1 5
2 2
7 5


Now I need to get the instructor with less courses. Which is this particular case is
2
.

I have tried using
HAVING count(*) = min(*)
but it gives a syntax error.

How should I get the min? Should I try a different approach?

Answer

You can use having like that (at least with Oracle) :

    SELECT instructor, count(*) cnt
    FROM courses
    GROUP BY instructor
    HAVING count(*) = (select min(count(*)) from courses group by instructor)
Comments