mysql - How can i use case alias in a select query

Question

In SQL how may I use case alias in a select query the one in question is a count, for example you will see below the select alias is set with as but i can not use the alias in the rest of the select query. Using MySQL.

The line in question below is: (win / total) * 100 as win_percent

    DECLARE @team TEXT;
    SET @team = "myTeam";
    SELECT 
        @team team,
        COUNT(CASE WHEN home = @team then 1 ELSE NULL END) as home,
        COUNT(CASE WHEN away = @team then 1 ELSE NULL END) as away,
        COUNT(CASE WHEN (away = @team or home = @team) then 1 ELSE NULL END) as total,
        COUNT(CASE WHEN winner = @team and (home = @team or away = @team)  then 1 ELSE NULL END) as win,    
        COUNT(CASE WHEN (home = @team or away = @team) and (winner <> @team and winner <> "0") then 1 ELSE NULL END) as lost,
        COUNT(CASE WHEN winner = "0" and (home = @team or away = @team) then 1 ELSE NULL END) as tie,
        (869 / 1928) * 100 as win_percent,
        (win / total) * 100 as win_percent
    from matches;

question from:https://stackoverflow.com/questions/65898742/how-can-i-use-case-alias-in-a-select-query

Answer 1

You can create a subquery and have that final win_percent in the outermost query. The query should look like this:

SELECT a.*,
    (win / total) * 100 as win_percent
FROM (
SELECT 
        @team team,
        COUNT(CASE WHEN home = @team then 1 ELSE NULL END) as home,
        COUNT(CASE WHEN away = @team then 1 ELSE NULL END) as away,
        COUNT(CASE WHEN (away = @team or home = @team) then 1 ELSE NULL END) as total,
        COUNT(CASE WHEN winner = @team and (home = @team or away = @team)  then 1 ELSE NULL END) as win,    
        COUNT(CASE WHEN (home = @team or away = @team) and (winner <> @team and winner <> "0") then 1 ELSE NULL END) as lost,
        COUNT(CASE WHEN winner = "0" and (home = @team or away = @team) then 1 ELSE NULL END) as tie,
        (869 / 1928) * 100 as win_percent
    from matches
) AS a