Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Counting the number of numbers in SQL

 
Amy Phillips
Ranch Hand
Posts: 280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1819
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amy Phillips
Ranch Hand
Posts: 280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike you're a gem!
Thanks very very much
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic