Two Laptop Bag*
The moose likes JDBC and the fly likes Counting the number of numbers in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Counting the number of numbers in SQL" Watch "Counting the number of numbers in SQL" New topic
Author

Counting the number of numbers in SQL

Amy Phillips
Ranch Hand

Joined: Apr 02, 2003
Posts: 280
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

Joined: Mar 25, 2001
Posts: 1746
    
    2
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

Joined: Apr 02, 2003
Posts: 280
Mike you're a gem!
Thanks very very much
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Counting the number of numbers in SQL
 
Similar Threads
doubt in a query
TreeSet behavior
Handling methods for an object that is held in an ArrayList
Class Diagram - How to model requirements ??
dynamically access images from database