SQL alias doesn't work

Question

I want to count all goals in a soccer match in the first and second halftime, but I only have the data for the first halftime and the final score.

SELECT SUM( scorehome_ht + scoreguest_ht ) AS halftime, 
    (SUM( scorehome_end + scoreguest_end ) - halftime) AS end 
FROM matches;

I'm new at stackoverflow, not familiar with MySQL and yes, I used google before :)

Answer 1

An alias assigned to a column can't be referenced by another expression in the SELECT list.

One workaround is to repeat the expression, and that's usually the most efficient, in terms of performance, e.g.

SELECT SUM( scorehome_ht + scoreguest_ht ) AS halftime, 
    SUM( scorehome_end + scoreguest_end ) - SUM( scorehome_ht + scoreguest_ht ) AS end 
FROM matches;

---

I said that was one workaround, because there are a couple of other workarounds available. It's also possible to use an inline view, since a query hast to reference the columns from the inline view by the name assigned to each column. This approach requires MySQL to run the inline view query, and materialize it as a temporary MyISAM table, and then the outer query runs against the MyISAM table, so this approach is less efficient.

Another option is to make use of MySQL user variables to hold the result of an expression:

SELECT @halftime := SUM( scorehome_ht + scoreguest_ht ) AS halftime, 
    SUM( scorehome_end + scoreguest_end ) - @halftime AS end 
FROM matches;

But this behavior is dependent on undocumented behavior; MySQL processes the expressions in the SELECT list in the order they are listed, so the value assigned to @halftime is available in expressions following the assignment. (References to @halftime BEFORE the assignment will get whatever value is there from a previous assignment (like from the previous row.)

Answer 2

You can't use aliases in the same select query, but what you can do is wrap them in a derived table:

SELECT halftime, scorehome_end + scoreguest_end - halftime AS end 
FROM
(
    SELECT scorehome_end, 
           scoreguest_end, 
           SUM( scorehome_ht + scoreguest_ht ) AS halftime
    FROM matches
) x;