Inner join with count and group by

Question

I have 2 tables

Timetable :
pupil_id, staff_id, subject, lesson_id

Staff_info :
staff_id, surname

The timetable table contains 1000s of rows because each student's ID is listed under each period they do.

I want to list all the teacher's names, and the number of lessons they do (count). So I have to do SELECT with DISTINCT.

SELECT DISTINCT TIMETABLE.STAFF_ID,
                COUNT(TIMETABLE.LESSON_ID),
                STAFF.SURNAME
FROM STAFF
INNER JOIN TIMETABLE ON TIMETABLE.STAFF_ID = STAFF.STAFF_ID
GROUP BY TIMETABLE.STAFF_ID

However I get the error:

Column 'STAFF.SURNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

This should do what you want:

SELECT s.STAFF_ID, COUNT(tt.LESSON_ID),
       s.SURNAME
FROM STAFF s INNER JOIN
     TIMETABLE tt
    ON tt.STAFF_ID = s.STAFF_ID
GROUP BY s.STAFF_ID, s.SURNAME;

Notes:

• You don't need DISTINCT unless there are duplicates in either table. That seems unlikely with this data structure, but if a staff member could have two of the same lesson, you would use COUNT(DISTINCT tt.LESSON_ID).
• Table aliases make the query easier to write and to read.
• You should include STAFF.SURNAME in the GROUP BY as well as the id.
• I have a preference for taking the STAFF_ID column from the table where it is the primary key.
• If you wanted staff with no lessons, you would change the INNER JOIN to LEFT JOIN.