SQL count based on two columns

Question

I have a training record table for staff (TRAINREC)

In this table it contains their employ_ref and the Course_Ref.

I need to count how many people have done all 5 available courses, how many have done 4, 3,2,1 and 0

It could be the case where they have done the same course more than once and so would have multiple entries for the same course in this table but it should still only count as one.

What would be the best method to count the entries (SQL Server) and count how many people have done "x" number of courses.

GarethD · Accepted Answer · 2014-12-10 14:27:57Z

Assuming your main table for staff is Employee, you need to do two different counts, the first, is how many courses each person has done:

SELECT  e.employ_ref,
        Courses = COUNT(DISTINCT tr.Course_Ref)
FROM    Employee AS e
        LEFT JOIN TrainRec AS tr
            ON tr.employ_ref = e.employ_ref
GROUP BY e.employ_ref;

Using DISTINCT would ensure that each different course is only counted once per employee.

Then you need to do a separate count, grouping on the Courses column from the first query:

SELECT  Courses,
        Employees = COUNT(*)
FROM    (   SELECT  e.employ_ref,
                    Courses = COUNT(DISTINCT tr.Course_Ref)
            FROM    Employee AS e
                    LEFT JOIN TrainRec AS tr
                        ON tr.employ_ref = e.employ_ref
            GROUP BY e.employ_ref
        ) AS t
GROUP BY Courses;

Collectives™ on Stack Overflow

SQL count based on two columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related