Craig Craig - 7 months ago 8
SQL Question

COUNT of different tables in GROUP

I have a table that holds a list of classes available at a school. Each class can have a number of sessions. And each class can have pupils assigned to it.

What I need to do is get a count of all sessions for each class, as well as the number of students attending the class. I have done the first bit, but if I join to the pupil allocation table, my counts will be wrong.

I have conjured up some fake SQL that you can use.

I'm stuck with efficiently getting a count from the Pupil link table.

DECLARE @Class TABLE
(
ClassID INT NOT NULL,
ClassName VARCHAR(20) NOT NULL
)

INSERT INTO @Class VALUES (1, 'English')
INSERT INTO @Class VALUES (2, 'Maths')

DECLARE @ClassSession TABLE
(
ClassSessionID INT NOT NULL,
ClassID INT NOT NULL,
Description VARCHAR(100) NOT NULL
)

INSERT INTO @ClassSession VALUES (1, 1, 'Basic English')
INSERT INTO @ClassSession VALUES (2, 1, 'Advanced English')
INSERT INTO @ClassSession VALUES (3, 1, 'Amazing English')
INSERT INTO @ClassSession VALUES (4, 2, 'Basic English')
INSERT INTO @ClassSession VALUES (5, 2, 'Basic English')

DECLARE @ClassPupil TABLE
(
ClassPupilID INT NOT NULL,
ClassID INT NOT NULL,
PupilID INT NOT NULL -- FK to the Pupils table.
)

INSERT INTO @ClassPupil VALUES (1, 1, 1000)
INSERT INTO @ClassPupil VALUES (2, 1, 1001)
INSERT INTO @ClassPupil VALUES (3, 1, 1002)
INSERT INTO @ClassPupil VALUES (4, 1, 1003)
INSERT INTO @ClassPupil VALUES (5, 1, 1004)

INSERT INTO @ClassPupil VALUES (6, 2, 1005)
INSERT INTO @ClassPupil VALUES (7, 2, 1006)
INSERT INTO @ClassPupil VALUES (8, 2, 1007)


SELECT ClassName, COUNT(*) AS Sessions, '??' AS NumerOfPupils
FROM @Class c
INNER JOIN @ClassSession cs
ON cs.ClassID = c.ClassID
GROUP BY c.ClassID, c.ClassName


It can maybe be done with a sub query? Is that the best way?

Answer

You have two independent dimensions for each class. You need to aggregat them separately:

SELECT c.ClassName, cs.Sessions, cp.Pupils
FROM @Class c INNER JOIN
     (SELECT ClassId, COUNT(*) as sessions
      FROM @ClassSession cs 
      GROUP BY ClassId
     ) cs
     ON cs.ClassID = c.ClassID INNER JOIN
     (SELECT ClassId, COUNT(*) as pupils
      FROM @ClassPupil cp
      GROUP BY ClassId
     ) cp
     ON cp.ClassId = c.ClassId;