I have two tables, one contains a list of subject areas and another which has these subjects allocated to it. The problem I have is I need to find out which subject area has the most courses allocated to it. I am attempting to count how many times each subjectID appears in the course table and then print the name of the maximum but am not having very much luck Heres what I have so far
select subject_id, subjectname from subject_area group by subject_id, subjectname having subject_ID = (select Max(subject_id) from course) but its giving me the number of occurences rather than the actual number which has these occurences. Thanks
I am attempting to count how many times each subjectID appears in the course table Note, however that nowhere in your original query are you actually performing a count. And selecting Max(subject_id) should be giving you the last subject_id based on the natural sort of the datatype (alpha for character, etc.). This turned out to be a surprisingly tricky query (if I fully understood the problem and if my solution is correct). I came up with something complicated using multiple inline views and aliasing. If somebody else can come up with something simpler in a single query, I'd *love* to see it. I'll alias your "course" table as "data" and your "subject_area" table as "lookup". We also need to alias the innermost select's count(*) (here as "mycount") so the enclosing select can see it. select data.subject_id , lookup.subjectname from course data inner join subject_area lookup on data.subject_id = lookup.subject_id group by data.subject_id , lookup.subjectname having count(*) = ( select max( mycount ) from ( select count(*) mycount from course group by subject_id ) ) Let me know if you need me to step you through this or break it apart.