CODEWITHSUNDEEP
sql
Ouyangzhi
Ouyangzhi

Reputation: 33

How to count students and classes of every teacher in SQL Server?

Assume 3 tables like this:

Teacher: 

teacher_id, name, ...

Student: 

student_id, teacher_id, ...

Class: 

class_id, teacher_id, ...

Is there a SQL query to get how many students and classes are assigned to every teacher?

Result should be something like this:

teacher_id  name    students    classes 
t_001       AAA        3           2 
t_002       BBB        1           2 
...

Upvotes: 3

Views: 12307

Answers (3)

dbenham
dbenham

Reputation: 130829

with teacher_students as (
  select t.teacher_id,
         t.name,
         count(s.student_id) as students
    from Teacher t
    left outer join Student s
      on t.teacher_id = s.teacher_id
   group by t.teacher_id, t.name
),
teacher_classes as (
  select t.teacher_id,
         count(c.class_id) as classes
    from Teacher t
    left outer join Class c
      on t.teacher_id = c.teacher_id
   group by t.teacher_id
)
select ts.teacher_id,
       ts.name,
       ts.students,
       tc.classes
  from teacher_students ts
  join teacher_classes tc
    on ts.teacher_id = tc.teacher_id

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

SELECT       T.TEACHER_ID
           , T.NAME,
           , COUNT(C.TEACHER_ID ) AS   CLASSES
           , COUNT(S.TEACHER_ID ) AS   STUDENTS
FROM TEACHER T 
LEFT OUTER JOIN CLASS C
ON  T.TEACHER_ID = C.TEACHER_ID
LEFT OUTER JOIN STUDENT S 
ON T.TEACHER_ID = S.TEACHER_ID
GROUP BY T.TEACHER_ID, T.NAME

Upvotes: -1

marc_s
marc_s

Reputation: 754488

Try something like this:

SELECT 
    t.teacher_id, t.name,
    Classes = (SELECT COUNT(*) FROM Class c WHERE c.teacher_id = t.teacher_id),
    Students = (SELECT COUNT(*) FROM Student s WHERE s.teacher_id = t.teacher_id)
FROM teacher t

Upvotes: 2

Related Questions