I'm trying to get a count of ethnicity for a teachers students. My first table contains the student number and ethnicity. The second table contains teacher id's, student numbers, course numbers, and etc… Each student can have a teacher more than 1 time.
Here's my query that is returning duplicate counts:
SELECT DISTINCT Count(StudentsSDRC.Ethnicity) AS CountOfEthnicity,
StudentsSDRC.Ethnicity
FROM StudentsSDRC
LEFT JOIN TeacherStudents
ON StudentsSDRC.StudentNum = TeacherStudents.StudentNum
WHERE (((TeacherStudents.teacherDCID)=43652))
GROUP BY StudentsSDRC.Ethnicity
Any help would be great.
Best Answer
SELECT DISTINCT
is not the answer to the issue. You are right that the join is the problem. It produces multiple rows for the same student, when the student is connect to a teacher more than once.One way to solve this is by using an
EXISTS
subquery:Other irrelevant to the problem notes / improvements:
LEFT JOIN
was acting as anINNER JOIN
as one of the right table's columns was in theWHERE
condition.dbo
).If you prefer the join for some reason, then this would work, too. I personally don't find it as explanatory as the 1st query.
It assumes that
StudentNum
isUNIQUE
in tableStudentsSDRC
: