0

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.

1
  • The field you select & group by should be the teacher?
    – James Z
    May 13, 2016 at 18:24

2 Answers 2

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 ;
0

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
2
  • Thank you for the quick answer, however, this still produced the same results.
    – David
    May 13, 2016 at 19:21
  • I would need to see sample data and expected results. This query will give you one row for every teacher / ethnicity combination. Thats what I thought you were looking for. Unless of course a teacher can have two different teacherDCID's. In which case you need to use the unique identifier in place of this field.
    – Joe
    May 13, 2016 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.