counting problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

counting problem

Author

Topic

obscuregirl
Starting Member

41 Posts

Posted - 2008-07-25 : 06:42:26

Hi, this is probably a bit of a dunce question but here goes!

I have the following piece of SQL which isn't quite working how I'd like it to:


Select tblstudent_course.Course_code, count(tblstudent_course.Course_code) as Returners 
from tblstudent_course, tblnew_entrants 
where tblstudent_course.student_id_number not in
   (Select tblnew_entrants.student_id_number
    from tblnew_entrants)
and tblstudent_course.residential_status_code <> 'O'
Group By tblstudent_course.Course_code
Order By tblstudent_course.Course_code

It should bring back a course code and 'Returners' should have a value of 1. Instead, 'Returners' has a value of 145665! I know that this is because it is counting all the ones that do not match tblstudent_course.student_id but I can't figure out how to get it just to count the one that doesn't match!

Any suggestions would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 06:45:53

do you mean this? if not post some sample data to illustrate what you want.

Select tblstudent_course.Course_code, count(tblstudent_course.Course_code) as Returners 
from tblstudent_course 
inner join tblnew_entrants 
on tblstudent_course.student_id_number=tblnew_entrants.student_id_numberwhere tblstudent_course.residential_status_code <> 'O'
Group By tblstudent_course.Course_code
Order By tblstudent_course.Course_code

obscuregirl
Starting Member

41 Posts

Posted - 2008-07-25 : 07:02:32

Hi, it's not quite what I want....

Example: I have two tables tblstudent_course and tblnew_entrants...

tblstudent_course contains the following records:
student_id_number , course_code
98014706 , BHCBSP3_1
97778184 , SASSP02_1
98002953 , SBPPRP2_5
222 , BSCCH01_1

tblnew_entrants contains:
student_id_number , course_code
98014706 , BHCBSP3_1
97778184 , SASSP02_1
98002953 , SBPPRP2_5

I want to count the records and bring back the course code for all those that appear in tblstudent_course but not tblnew_entrants so my result should be:

BSCCH01_1 , 1

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 07:43:16

[code]SELECT sc.course_code,COUNT(sc.student_id_number)
FROM tblstudent_course sc
LEFT OUTER JOIN tblnew_entrants ne
ON ne.student_id_number=sc.student_id_number
WHERE ne.student_id_number IS NULL
GROUP BY sc.course_code[/code]

obscuregirl
Starting Member

41 Posts

Posted - 2008-07-25 : 09:03:54

Thanks visakh16, that's working fine now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources