5

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

4

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.

5

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.

  • i see,so simply put I can use the alias from a subquery or in the where clause after it has been executed right? @MaxVernon – Manny265 Jan 5 '16 at 15:38
  • 1
    Yes, you can only use the alias once the data for the column has been materialized by the engine. – Max Vernon Jan 5 '16 at 15:41
  • And how about aliases for tables, especially if some table is joined with itself? And what if different tables have fields of the same name? Aliases are applied for disabmiguation before statement will be executed. And I like to replace table names by single-char aliases for briefing the code. – Kondybas Apr 19 '17 at 10:18
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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.