jpuriol jpuriol - 1 year ago 55
SQL Question

Getting min from GROUP BY clause

I have the following table


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:

1 5
2 2
7 5

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

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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download