Prevent duplicate record counts in one to many query

Question

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.

Answer 1

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:

SELECT Count(*) AS CountOfEthnicity, 
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
WHERE EXISTS
      ( SELECT *
        FROM dbo.TeacherStudents AS t
        WHERE s.StudentNum = t.StudentNum
          AND t.teacherDCID = 43652
      )
GROUP BY s.Ethnicity ;

Other irrelevant to the problem notes / improvements:

• The LEFT JOIN was acting as an INNER JOIN as one of the right table's columns was in the WHERE condition.
• It's good to alias your tables with shorter names. Improves readability of the queries.
• It's good to prefix the tables with the schema names (eg. dbo).
• The pile of parentheses was not needed at all.

---

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 is UNIQUE in table StudentsSDRC:

SELECT Count(DISTINCT s.StudentNum) AS CountOfEthnicity, 
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
    JOIN dbo.TeacherStudents AS t
        ON s.StudentNum = t.StudentNum
WHERE t.teacherDCID = 43652
GROUP BY s.Ethnicity ;

Answer 2

You need to group on teacher as well so that the counts roll up correctly and you will only get one record per teacher. Without your where clause, students can be counted multiple times, once for each teacher, which is correct if you are counting student ethnicity per teacher.

Select TeacherStudents.teacherDCID, Count(*) AS CountOfEthnicity, StudentsSDRC.Ethnicity
    FROM StudentsSDRC 
    LEFT JOIN TeacherStudents ON StudentsSDRC.StudentNum = TeacherStudents.StudentNum
    WHERE (((TeacherStudents.teacherDCID)=43652))
    GROUP BY TeacherStudents.teacherDCID, StudentsSDRC.Ethnicity