Inner select statements

Mike55
10-15-2004, 05:33 AM
Hi all

am attempting to do an inner select statement on a table, the table structure is as follows:
StudentID, Course_Name, Year

What i want to do is to count the no. of students on each course. The query must be setup in such a way that we get the course name from the table itself.

SELECT Distinct(Course_Name), Count(StudentID) as Population
FROM Details
where (course_Name) = (Select Distinct(Course_Name) From Details)

But this statement doesn't seem to work...any suggestions.

Mike55

fujih
10-15-2004, 06:08 AM
If you are trying to do an inner select then use the IN keyword rather than =

eg.

SELECT * FROM Details
Where course_name IN (select distinct course_name from details)

however if you want to get the number of students on each course it would be better for you to use a GROUP BY clause

e.g.

SELECT course_name, count(studentid) AS population
FROM Details
GROUP BY course_name


Hi all

am attempting to do an inner select statement on a table, the table structure is as follows:
StudentID, Course_Name, Year

What i want to do is to count the no. of students on each course. The query must be setup in such a way that we get the course name from the table itself.

SELECT Distinct(Course_Name), Count(StudentID) as Population
FROM Details
where (course_Name) = (Select Distinct(Course_Name) From Details)

But this statement doesn't seem to work...any suggestions.

Mike55

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum