SQLGroup with Distinct count

I have query

select count(distinct(a.studentid)count,
    case
      when a.Age <=19 then '1-19'
      when a.Age between 20 and 29 then '20-29'
    end as age_range
from table a 
where 0 = 0
group by a.age

Outcome

count  age_range
 10      1-19
  5      1-19
 12      20-29
 18      20-29

Not sure why not group by range and expect results. Thank you.

 count   age_range
 15       1-19
 30       20-29

Answer

I would suggest cross apply to define the alias:

select v.age_range, count(distinct a.studentid)count,
from table a cross apply
     (values (case when a.Age <= 19 then 'Under 12 months-19'
                   when a.Age between 20 and 29 then '20-29'
              end)
     ) as v(age_range)
group by v.age_range;

SQL Server doesn’t allow aliases as GROUP BY keys. I think defining the alias in the FROM clause is the simplest method; you could also use a CTE or subquery or repeat the case expression.

I should also note that the between is not needed for the case. The clauses are evaluated in order so you could use:

(case when a.Age <= 19 then 'Under 12 months-19'
      when a.Age <= 29 then '20-29'
 end)