Case function with an alias-CodePudding

I wish to use CASE FUNCTION with an alias that resulted of a quotient of two columns. The code that I wrote is the follow, but it return an error. Could anyone help me with this?

SELECT TOP 5(ROUND(Registered_Students/Total_Student * 100,2)) AS Porcentaje, C.Total_Student, C.Registered_Students, S.Subject_Name, DATEPART(Year, C.Date) AS Año,
    CASE WHEN Porcentaje >= 75 THEN 'Elected'
         WHEN Porcentaje >= 50 THEN 'Elected 1'
         ELSE 'Not elected' AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje

CodePudding user response：

the syntax of SQL server case statement is not correct:

SELECT column1,
             column2,
               CASE WHEN CONDITION THEN 'Value1'
               ELSE 'Value2' END AS columnX
  FROM table

you just need to add END in your syntax and it will work fine

CodePudding user response：

you have to insert the word; 'END' end of the line

SELECT C.Total_Student, C.Registered_Students, S.Subject_Name, DATEPART(Year, C.Date) AS Año,
    CASE WHEN Porcentaje >= 75 THEN 'Elected'
         WHEN Porcentaje >= 50 THEN 'Elected 1'
         ELSE 'Not elected' END AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje

as-is

ELSE 'Not elected' AS Elections

to-be

ELSE 'Not elected' END AS Elections

CodePudding user response：

The standard case syntax is

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

You can fix your query by writing like this

Query will be easier to understand
Different indentation makes it easier to modify and to read

SELECT TOP 5
  (ROUND(Registered_Students / Total_Student * 100, 2)) AS Porcentaje
, C.Total_Student
, C.Registered_Students
, S.Subject_Name
, DATEPART(YEAR, C.Date) AS Año,
    (CASE
        WHEN Porcentaje >= 75 THEN 'Elected'
        WHEN Porcentaje >= 50 THEN 'Elected 1'
        ELSE 'Not elected' END) AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje