SQL AVG Alias with CASE

Question

I am trying to run this query, but facing an unknown column error for Average:

SELECT CAST(AVG(Lesson_CLass.T_Points) AS Decimal (10,0)) AS Average,
       CASE WHEN Lesson_CLass.T_Points = Average THEN 'On Target'
            WHEN Lesson_CLass.T_Points < Average THEN 'Above Target'
            ELSE 'Below Target'
       END AS Progress
FROM Lesson_CLass

I am unsure as to why the Alias is unknown?

Answer 1

The column alias isn't available in the same SELECT clause as it's defined.

With a derived table, you can use the alias in the case expression:

SELECT Average,
       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.T_Points,
           CAST(AVG(Lesson_CLass.T_Points) AS Decimal (10,0)) AS Average
    FROM Lesson_CLass
) dt

Answer 2

I am guessing here: You are probably trying to compare single rows with the overall average value.

The classic way with a subquery for the total average:

select
  lc.*,
  av.avg_t_points,
  case 
    when lc.t_points = av.avg_t_points then 'On Target'
    when lc.t_points < av.avg_t_points then 'Above Target'
    else 'Below Target'
  end as progress
from lesson_class lc
cross join
(
  select avg(t_points) as avg_tpoints
  from lesson_class
) as av;

The modern way with an analytic function for the total average:

select
  with_avg.*,
  case 
    when t_points = avg_t_points then 'On Target'
    when t_points < avg_t_points then 'Above Target'
    else 'Below Target'
  end as progress
from 
(
  select lc.*, avg(t_points) over () as avg_tpoints
  from lesson_class
) with_avg;