I have the following script

SELECT  
    CASE
        WHEN Grade < 4 
            THEN 'Fail'
        WHEN Grade >=4 and Grade < 6 
            THEN 'C'
        WHEN Grade >=6  and Grade < 8
            THEN 'Ok'
        ELSE 'Exc.'
    END AS [Status],
    COUNT(Grade) AS v
FROM
    GradeList
GROUP BY 
    [Status]

And it doesn't see the column Status. Can't get why, what's the problem

CodePudding user response：

That is how SQL Server (and SQL) are defined. Column aliases defined in the SELECT are not available in the FROM, WHERE, or GROUP BY clauses.

There are multiple solutions for this. My favorite is to use APPLY because this defines the aliases in the FROM clause:

SELECT v.Status, COUNT(*) as v
FROM GradeList gl CROSS APPLY
     (VALUES (CASE WHEN Grade < 4 THEN 'Fail'
                   WHEN Grade < 6 THEN 'C'
                   WHEN Grade < 8 THEN 'Ok'
                   ELSE 'Exc.'
              END)
     ) v(Status)
GROUP BY v.Status;

Note that this also simplifies the CASE logic. The first matching condition is returned, so there is no need for BETWEEN. That actually makes it much easier to add and remove conditions, if you want to tweak the query.

CodePudding user response：

SQL Server processes the SQL Statment in a particular logical order. As SELECT clause comes after GROUP BY clause, GROUP BY clause does not see the column alias.

Read more on the logical processing order on MSDN

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

You can go for derived table or Common Table Expression to get over this problem.

SELECT Status, COUNT(Grade) AS v
FROM
(
SELECT  
    CASE
        WHEN Grade < 4 
            THEN 'Fail'
        WHEN Grade >=4 and Grade < 6 
            THEN 'C'
        WHEN Grade >=6  and Grade < 8
            THEN 'Ok'
        ELSE 'Exc.'
    END AS [Status],
    Grade     
FROM
    GradeList
) AS g(Status,Grade)
GROUP BY 
    [Status]

;WITH cte_gradelist(Status,Grade) AS
(
SELECT  
    CASE
        WHEN Grade < 4 
            THEN 'Fail'
        WHEN Grade >=4 and Grade < 6 
            THEN 'C'
        WHEN Grade >=6  and Grade < 8
            THEN 'Ok'
        ELSE 'Exc.'
    END AS [Status],
    Grade     
FROM
    GradeList
)
SELECT Status, COUNT(Grade) AS v
FROM cte_gradelist
GROUP BY 
    [Status]