Unable to ALIAS a simple CASE statement

user_1860656 0
Viewed 225

Hi everyone I am very new to MySQL server and trying to complete an assignment for class. I cant seem to find the solution. Any help is much appreciated. Here is my code and here is the error statement. Thank you in advance.

ERROR Statement:

ERROR 1054 (42S22) at line 1: Unknown column 'Total_Number_Forced_Outage_Events' in 'field list'

SELECT 
 
SUM(CASE 

WHEN Reason = 'Forced' THEN 1 ELSE 0 END) AS Total_Number_Forced_Outage_Event,

Count(*)AS Total_Number_Outage_Events,

Total_Number_Forced_Outage_Events / Total_Number_Outage_Events AS Forced_Outage_Percentage,

YEAR(Start_Time) AS Year

FROM AEMR 

WHERE Status = 'Approved' 

GROUP BY Year 

ORDER BY Year 

;
mysql
2 Answers

As @Akina commented above, you cannot make reference to an alias in the same query where it is defined.

You can, however, define an alias in a subquery and use it in the outer query:

SELECT t.*,
  Total_Number_Forced_Outage_Events / Total_Number_Outage_Events
    AS Forced_Outage_Percentage
FROM (
    SELECT 
      SUM(CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END)
        AS Total_Number_Forced_Outage_Events,
      COUNT(*) AS Total_Number_Outage_Events,
      YEAR(Start_Time) AS Year
    FROM AEMR 
    WHERE Status = 'Approved' 
    GROUP BY Year 
) AS t
ORDER BY Year;

t in this example is the table alias for the subquery.

Exception: MySQL allows you to use an alias defined in the same query in the GROUP BY, HAVING, and ORDER BY clauses.

Also read documentation about column aliases in https://dev.mysql.com/doc/refman/8.0/en/select.html and https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

Bill_Karwin_208600

CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END is exactly the same as Reason = 'Forced', since MySQL (and MariaDB) use 1 and 0 as boolean values.

So you can just use SUM(Reason = 'Forced') to get Total_Number_Forced_Outage_Events.

Also, COUNT(Reason = 'Forced') will give you Total_Number_Outage_Events since both ones and zeroes will be counted (This is not particularly useful in your case but it explains why the next sentence is true).

Finally, AVG(Reason = 'Forced') will give you Forced_Outage_Percentage (since AVG(x) is essentially SUM(x) / COUNT(x)).

So one way to write your query is:

SELECT 
  SUM(Reason = 'Forced') AS Total_Number_Forced_Outage_Event,
  COUNT(Reason = 'Forced') AS Total_Number_Outage_Events,
  AVG(Reason = 'Forced') AS Forced_Outage_Percentage,
  YEAR(Start_Time) AS Year
FROM AEMR 
WHERE Status = 'Approved' 
GROUP BY Year 
ORDER BY Year 
;

which I believe is concise enough.

Note that writing COUNT(Reason = 'Forced') instead of COUNT(*) may be more efficient since the optimizer will probably see that SUM(x), COUNT(x) and AVG(x) are used within the same scope and do the three calculations at once.

user_5850800
Related