How and when do I use an alias in my SQL statement

Question

I have a query in which I do some calculation and then I give that column an alias. In the next column I would like to use the result of that calculation in an IF statement. MySQL did not recognise the alias when I used it as a condition but instead required me to rewrite the whole query, put some brackets around it and then carry out the condition checking.
Here is the SQL query:

SELECT StudentId
    ,SubjectID
    , (COUNT(StudentId) / (
        SELECT COUNT(SubjectID) 
        FROM lectureattendancein 
        WHERE SubjectID ='MIS4' ) * 100) AS Percentage
    , IF((COUNT(StudentId) / (
        SELECT  COUNT(SubjectID) 
        FROM lectureattendancein  
        WHERE SubjectID ='MIS4' 
        )* 100) >= 80, 'ALLOWED', 'NOT ALLOWED') AS ExamAdmit
FROM attendancein 
GROUP BY StudentId, SubjectID 
HAVING SubjectID='MIS4' 
ORDER BY StudentId ASC

I would like to use the column alias called Percentage in the IF statement

Hannah Vernon · Answer 1 · 2016-01-05T15:28:48.023

Structured Query Language is a declarative language. Declarative languages don't tell the computer how to run, they simply describe the desired result. The implication here is the alias is only important for the results, not the execution of the query.

Since MySQL (and most other RDBMSs) only apply aliases after the query has completed, MySQL will complain if you attempt to use an alias in another column since it doesn't actually understand what the alias represents until the query completes.

Lennart - Slava Ukraini · Accepted Answer · 2016-01-05T18:45:11.063

In addition to @Max Vernons answer the logical order of evaluation of a SQL query is:

1. FROM / JOIN clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5a. SELECT clause
5b. Renamning (i.e. alias)
6. ORDER BY clause

You can for example reference an alias in the ORDER BY clause, but not in a GROUP BY or SELECT clause.

One possibility is to nest your query:

SELECT StudentId
     , SubjectId
     , Percentage
     , CASE WHEN Percentage > 80 
            THEN 'ALLOWED' 
            ELSE 'NOT ALLOWED'
       END AS ExamAdmit
FROM (
    SELECT StudentId
         , SubjectID
         , (COUNT(StudentId) / (SELECT COUNT(SubjectID) 
                                FROM lectureattendancein 
                                WHERE SubjectID ='MIS4' ) * 100
           ) AS Percentage
    FROM attendancein 
    GROUP BY StudentId, SubjectID 
    HAVING SubjectID='MIS4'
) as t
ORDER BY StudentId ASC

As a side note the DBMS can evaluate the query in any order that it finds appropriate, as long as it preserves the semantics of the logical evaluation.

score 1 · Answer 3 · edited Jan 06 '16 at 00:25

1

You could introduce a variable to store the result of the calculation, so that you could reference it when the result is needed elsewhere in the same SELECT clause:

SELECT ...,
       @percentage := ... AS Percentage,
       IF (@percentage >= 80, 'ALLOWED', 'NOT ALLOWED') AS ExamAdmit
    FROM ...

edited Jan 06 '16 at 00:25

Andriy M

23,261
6
60
103

answered Jan 05 '16 at 19:30

Rick James

80,479
5
52
119

How and when do I use an alias in my SQL statement

3 Answers3