Cannot perform an aggregate function on an expression containing an aggregate or a subquery. SQL Server

Question

We got some records in a table Employee. And different Students are linked with a particular ClassId. In the screenshot, StudentId 5,6 belongs to ClassId 1, while StudentId 5,6,7 belongs to another ClassId 2.

I want to fetch the ClassId from above table that only contains StudentId 5 and 6. So in above example it should return ClassId 1. The tricky part is that the value 5 and 6 is coming from another table.

So when I run below query it throws an error

SELECT ClassId
FROM employee
GROUP BY ClassId
HAVING COUNT(CASE WHEN StudentId NOT IN (select id from test) THEN 1 END) = 0;

Error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

If I remove the sub query part then it works fine

SELECT ClassId
FROM employee
GROUP BY ClassId
HAVING COUNT(CASE WHEN StudentId NOT IN (5,6) THEN 1 END) = 0;

Below are the create table scripts for both of above tables

CREATE TABLE Student (Id INT) 
INSERT INTO Student VALUES (5),(6)

CREATE TABLE Employee
(
     Id INT IDENTITY, 
     ClassId INT, 
     StudentId INT
)

INSERT INTO employee 
VALUES (1, 5), (1, 6), (2, 5), (2, 6), (2, 7)

Can someone suggest a workaround to run the first query without error?

Answer 1

Use group by and having like this:

SELECT e.ClassId
FROM employee e LEFT JOIN
     test t
     ON t.StudentId = e.id
GROUP BY e.ClassId
HAVING COUNT(*) = COUNT(t.StudentId) AND      -- all rows in `e` match a row in `t`
       COUNT(*) = (SELECT COUNT(*) FROM test) -- all match

Answer 2

This is not allowed, but you can do left join. then use coalesce() to check those which are not in test table.

select t1.ClassId 
from employee t1
left join test t2 on t2.id = t1.StudentId
group by t1.ClassId
having count(case when coalesce(t2.id, 0) = 0 then 1 end)= 0

Answer 3

You can use apply to simplify the HAVING clause.

declare @Student table (ID int) ; 
insert into @Student values (5),(6) ;
declare @employee table (ID int identity, ClassId int, StudentId int)  ; 
insert into @employee values (1,5),(1,6), (2,5),(2,6), (2,7) ; 


SELECT e.ClassId  AS ClassId , COUNT(e.StudentId)  
FROM @employee AS e 
CROSS APPLY ( 
    SELECT ID
    FROM @Student AS s
    WHERE s.ID = e.StudentId 
) AS student 
GROUP BY e.ClassId 
HAVING COUNT(e.StudentId) = (SELECT COUNT('8') FROM @Student) ;