通过别名分组嵌套查询

Question

我正在尝试按语句使用一个小组，以计算每个进度等级：

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;

但是，我面对“ group roch”中未知专栏的“进度”

Answer 1

使用以下操作：如果您不需要小组，

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

请使用此信息：如果您要组

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]