Excellent QOD. I spent enough time studying the code and the results in QA that I don't feel guilty about running it before choosing the correct answer :Whistling:.

The light bulb finally went on for me that including "subgrp" in the result set is a red herring of sorts -- none of the three counts change depending on its value. The results are easier to understand by removing subgrp and making them distinct. If we make the Select statement....

select distinct grp --,subgrp

,count(*) OVER (partition by grp,subgrp) grpcount

,count(*) OVER (partition by grp) subcount

,count(*) OVER (partition by NULL) grandcount

from #windows

group by grp , subgrp

Then our results are much more straight-forward and, to me anyway, easier to tie back to the query:

grp grpcount subcount grandcount

----- ----------- ----------- -----------

aaaaa 1 3 4

bbbbb 1 1 4

Putting the results in plain English,

there's 1 grp "aaaaa" with 3 subgrps out of 4 total subgrps,

there's 1 grp "bbbbb" with 1 subgrp out of the same total 4 subgrps

The grpcount will always be 1 since we did "group by" and windowed on the same combination of grp and subgrp.