MySQL – Reuse Select Aliases

aliasMySQLsubquery

I currently have a query where I'm doing two subqueries to get X, Y data:

SELECT
  t.series AS week,
  ( ... ) X,
  ( ..., AND ... ) Y,
  ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp t

Y is kind of subset of X, since I apply just an additional condition to the existing ones, if X is:

SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY

Then Y has an additional AND condition:

SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
AND t1.some_state = 'x state'

And for the value of X I need to take those two results – X and Y and do some calculation. Since I can't use the aliases, I have to use a subquery, right? But in that case it seems too much.

Is there a way to reuse those subqueries? It seems to be too much of the same.

I'm using MySQL 5.6 so I'm not able to use CTEs 🙁

PS: series_tmp comes from this wonderful idea (thanks to them).

Best Answer

The SQL standard does not allow the reuse of the alias here.

However, MySQL uses an extension to the standard that enables you to do something close, namely use a previously defined alias in a subquery:

select some_expression as alias_name, (select alias_name)

which in your case would mean that you can do

SELECT
   t.series AS week,
   ( ... ) X,
   ( ... AND ... ) Y,
   ROUND( (select X) * 100) / (select Y), 2) Z
FROM series_tmp t

The alias should not match a column name, as that would take precedence, and it won't work for aggregates. Similar to nbk's answer, this is specific to MySQL and won't work in other database systems.

A solution that works in other database systems too and that you, according to your comment, already found, would be to introduce a derived table, e.g.

select week, X, Y,  ROUND(X * 100 / Y, 2) Z
from (
  SELECT
    t.series AS week,
    ( ... ) X,
    ( ..., AND ... ) Y
  FROM series_tmp t
) as sub
Related Question