How can I change the code to run the way I want?-CodePudding

I'm trying to solve the "Gradebook Challenge" from KhanAcademy but not in the platform but in SQL Management Studio. And I haven't obtain the same result as on the platform.

This is my code and the error that appears when I try to run it in SQL Management Studio. Yes, I've done the research and now I realise that is not posible use the GROUP BY clause with alias column, but I don't know what else do. So please, I really need a hand on this.

SELECT COUNT(*), Grade,
    CASE
        WHEN Grade > 90 THEN 'A'
        WHEN Grade > 80 THEN 'B'
        WHEN Grade > 70 THEN 'C'        
        ELSE 'F'
    END AS LetterGrade
    FROM StudentsGrades
    GROUP BY Grade

CodePudding user response：

Push the query containing the CASE statement into a subquery or Common Table Expression, eg

use tempdb
go
create table StudentsGrades(Id int identity primary key, StudentId int, Grade int);

insert into StudentsGrades(StudentId,Grade) 
    values (1,90),(2,99),(3,40),(5,88);


with q as
(
    SELECT *, 
    CASE
        WHEN Grade > 90 THEN 'A'
        WHEN Grade > 80 THEN 'B'
        WHEN Grade > 70 THEN 'C'        
        ELSE 'F'
    END AS LetterGrade
    FROM StudentsGrades
)
SELECT LetterGrade, Count(*) CountOfGrade
from q
group by LetterGrade

outputs

LetterGrade CountOfGrade
----------- ------------
A           1
B           2
F           1

(3 rows affected)

CodePudding user response：

Just another option is CROSS APPLY. It allows you to stack calculations and reference the alias.

Example

Select LetterGrade
      ,CountofGrade = count(*)
 From  StudentsGrades A
 Cross Apply ( values ( CASE WHEN Grade > 90 THEN 'A'
                             WHEN Grade > 80 THEN 'B'
                             WHEN Grade > 70 THEN 'C'        
                             ELSE 'F'
                         END
                       ) )B(LetterGrade)
Group By LetterGrade

Results

LetterGrade CountofGrade
A           1
B           2
F           1