Grouping Nested Query BY CASE OF ALIAS

Question

I am trying to use a GROUP BY statement that would count each of the progress grades:

SELECT
with_avg.*, 
CASE
when T_Points = Average then 'On Target'
when T_Points < Average then 'Above Target'
ELSE 'Below Target'
END AS progress, COUNT(progress)
FROM 
(
SELECT Lesson_CLass.*, avg(t_points) over () AS Average
FROM Lesson_CLass GROUP BY progress
) with_avg;

However, I am faced with Unknown column 'progress' in 'GROUP clause'

Answer 1

use this : if you don't need group by

select *, COUNT(progress) over() as prccnt
from (
    SELECT
    with_avg.*, 
    CASE
    when T_Points = Average then 'On Target'
    when T_Points < Average then 'Above Target'
    ELSE 'Below Target'
    END AS progress
          FROM 
          (
             SELECT Lesson_CLass.*, avg(t_points) over () AS Average
             FROM Lesson_CLass 
          ) with_avg;
)main

use this : if you want group by

select *,COUNT(progress) as prccnt
from (
    SELECT
    with_avg.*, 
    CASE
    when T_Points = Average then 'On Target'
    when T_Points < Average then 'Above Target'
    ELSE 'Below Target'
    END AS progress
          FROM 
          (
             SELECT Lesson_CLass.*, avg(t_points) over () AS Average
             FROM Lesson_CLass 
          ) with_avg;
)main
group by [column name]