CODEWITHSUNDEEP
sqlcase
Martín Santos
Martín Santos

Reputation: 3

Case function with an alias

I wish to use CASE statement with an alias that resulted of a quotient of two columns. The code that I wrote is the follow, but it returns 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

Upvotes: 0

Views: 84

Answers (4)

jarlh
jarlh

Reputation: 44776

A column alias can't be referenced in the same SELECT clause as it is created. Have a derived table (i.e. subquery in the FROM clause), where you create the Porcentaje column.

SELECT TOP 5 dt.*,
       CASE WHEN Porcentaje >= 75 THEN 'Elected'
            WHEN Porcentaje >= 50 THEN 'Elected 1'
            ELSE 'Not elected'
       END AS Elections
FROM
(
    select
        (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
    FROM Cohort AS C
    INNER JOIN Subject AS S
    ON S.Id_Subject = C.Id_Subject
) dt
ORDER BY Porcentaje

Upvotes: 0

sanampakuwal
sanampakuwal

Reputation: 528

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

Upvotes: 0

Huda khan
Huda khan

Reputation: 85

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

Upvotes: 2

Joonseo Lee
Joonseo Lee

Reputation: 486

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

Upvotes: 1

Related Questions