CODEWITHSUNDEEP
t-sqlcountgroup-byaggregate-functions
Robert Fakes
Robert Fakes

Reputation: 97

Inner join with count and group by

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.

Upvotes: 7

Views: 21041

Answers (4)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

When using Aggregate function(Count, Sum, Min, Max, Avg) in the Select column's list, any other columns that are in the Select column's list but not in a aggregate function, should be mentioned in GROUP BY section too. So you need to change your query as follow and add STAFF.SURNAME to GROUP BY section too:

SELECT 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,STAFF.SURNAME

Distinct is useless also in your scenario. and also as you are going to show the teachers name and Count lessons, you do not need to add TIMETABLE.STAFF_ID to Select's column's list,, but it should remain in Group By section to prevent duplicate names.

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

You may need to take a look at this W3C post for more info

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 10

Zohar Peled
Zohar Peled

Reputation: 82474

Another option:

SELECT DISTINCT si.staff_id, surname, COUNT(lesson_id) OVER(PARTITION BY staff_Id)
FROM Staff_info si
INNER JOIN Timetable tt ON si.staff_id = tt.staff_id

Upvotes: 1

Devart
Devart

Reputation: 121922

SELECT T.STAFF_ID,
       T.CNT,
       S.SURNAME
FROM STAFF S
JOIN (
    SELECT STAFF_ID, CNT = COUNT(/*DISTINCT*/ LESSON_ID)
    FROM TIMETABLE
    GROUP BY STAFF_ID
) T ON T.STAFF_ID = S.STAFF_ID

Upvotes: 2

Related Questions