MSSQL can't understand what's happening with the action "having count(*) lesser than <some field of other table>"

Question

I've tried to understand some part of an exercise i'm doing and just couldn't get it.

There's a part where 'T' is selected, grouped by 'a' and than it's redirected to "having count(*) < T3.a", and I don't know how to approach it.

I've tried googling this sort of thing and see if there are similar examples but all other examples were using regular numbers for ex.: "having count(*) < 5" and not whole fields for comparison.

The exercise is this:

MSSQL exercise

create table T(a int, b int);
insert into T values(1,2);
insert into T values(1,1);
insert into T values(2,3);
insert into T values(2,4);
insert into T values(3,4);
insert into T values(4,5);

select T3.b, (select count(T5.a) 
              from T T5 
              where T5.a = T3.b)
from (select T1.a as a, T2.b as b 
      from T T1, T T2 
      where T1.b < T2.a) as T3
where not exists (select T4.a 
                  from T T4
                  group by T4.a
                  having count(*) < T3.a);

I thought that the having count(*) was comparing each value that was grouped by to each value of T3.a in each row and if all rows have met the criteria than the value is getting selected but I somehow get different results.

Can someone please explain to me what is really going on behind this "having count(*) < T3.a" operation?

Thank you in advance.

Answer 1

To repeat myself from the comments, a HAVING is like a WHERE for aggregate functions. You cannot use aggregate function in the WHERE, for example WHERE SUM(SomeColumn) > 5, so you need to do them in the HAVING: HAVING SUM(SomeColumn) > 5. This would returns any rows where the SUM of the column SomeColumn is greater than 5 in the group.

For your expression, HAVING COUNT(*) < T3.a it would only return rows where the value of COUNT(*) is less than the value of T3.a.