CODEWITHSUNDEEP
sqlms-access
James JPN
James JPN

Reputation: 61

SQL Query GROUP BY MIN?

I'm very new to SQL just started learning yesterday so please accept my apologies if this is, in fact, an annoying newb question. I'm studying SQL as part of a college course.

I have a flat student database that I had to import into MSaccess. I have been given this task:

The courses with the least number of enrollments

This is what my database looks like:

enter image description here

As you can see by looking at the Enrollments there are 10 courses with 8 enrollments. My question is, how do I group all the minimum values in the Enrollments column?

I first tried this: 

SELECT 
    MIN Enrolments AS Leastnumberofstudents 
FROM 
    DataFIle;

But of course, that only returns the minimum value of that column which is 8.

Then I tried: 

SELECT [Course Name], MIN (Enrolments) AS [Least Number of Students]
FROM DataFile
GROUP BY (Enrolments), [Course Name]
ORDER BY (Enrolments), [Course Name];

But that returns all of the courses not only the ones with the minimum value.

enter image description here

I also thought about just using WHERE = 8 but I don't think that's what I'm being asked to do.

Any help or nudge in the right direction would be so apprenticed and again apologies

Upvotes: 0

Views: 267

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Just use order by and select top:

select top 1 df.*
from datafile as df
order by enrolments asc;

This assumes you are using MS Access.

Upvotes: 1

CCBet
CCBet

Reputation: 426

Something like:

SELECT * FROM DataFile WHERE Enrolments = (SELECT MIN(Enrolments) FROM DataFile);

Upvotes: 1

Related Questions