Support NeoGAF

[Vlad]

Ok, I'm taking a Relational Databases class, and while I feel I've got a pretty good grasp on the stuff so far, I seem to be having issues with a question which seems like it'd be really simple.

The table in question is called "Student", and contains a whole bunch of different information. The question that is giving me way more trouble than it reasonably should is:

Which major has the greatest number of non-degree students and how many?

Now, the Student table contains information about each individual student, including their major, and their status (Undergrad, Grad, or "non" for non-degree).

I figure that this can be easily accomplished by using a subquery. I get the following query up and running just fine:

select Major, count(*)
from Student
where Status = 'Non'
group by Major;

This returns a table of the numbers of non-degree students in different majors. I figure, "hey, now all I have to do is find the maximum value in the count column". So I try doing a nested query, and this is where everything pretty much falls apart. I try just making the following query:

select Major, Max(Num)
from (select Major, count(*) AS Num
from Student
where Status = 'Non'
group by Major);

When I try running this, I get the following error, pointing to the Major in the first Select command:
ORA-00937: not a single-group group function

I'm sure there's a simple explanation, but I can't seem to find it. Anybody feel like throwing me a bone and telling me why this isn't working?

 

[sefskillz]

if i understand the question right, try finding the max count from the set first and doing a where on that value. i think the problem you're having relates to joining the major values and a single value of max.

 

[iapetus]

The obvious answer is that you need a GROUP BY Num on your outer select. Not sure if that will make it work, but it should do.

In some DBs there's a cheap and easy way to do this:

SELECT major, COUNT(*) FROM students
ORDER BY COUNT(*) LIMIT 1;

Not sure if that's standard, though.