counting problem

Question

counting problem

Jude Lynn

SSC Veteran

Points: 279
More actions
July 25, 2008 at 4:43 am

#194628

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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Jude

The huge count is from the cross join (tblstudent_course, tblnew_entrants with nothing in the WHERE clause to join them).

All rows in tblstudent_course which don't have a match in tblnew_entrants - let's say 400 out of 500 - are then matched row-for-row with tblnew_entrants. If there are 20 rows in tblnew_entrants then your count would be 500*20.

You could fix this query by taking ", tblnew_entrants " out of the FROM clause. Better still, write the query in a more efficient way:

Select c.Course_code, count(*) as Returners

FROM tblstudent_course c

LEFT JOIN tblnew_entrants n

ON n.student_id_number = c.student_id_number

WHERE c.residential_status_code <> 'O'

AND n.student_id_number IS NULL

Group By c.Course_code

Order By c.Course_code

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jude Lynn SSC Veteran Points: 279 More actions · Answer 2

Jude Lynn

SSC Veteran

Points: 279

July 25, 2008 at 7:03 am

#848348

Thanks Chris, that's working fine!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome Jude. To consolidate what you've picked up from this snippet, I'd strongly recommend you read "Using Joins" in BOL.