Unable to ALIAS a simple CASE statement

Question

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 

;

Answer 1

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.