MySQL reuse select aliases

Question

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).

score 8 · Accepted Answer · answered Sep 06 '20 at 00:23

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

nbk · Answer 2 · 2020-09-03T21:23:22.270

For that you can use user defined variables

SELECT 
    t.series AS week,
    @x:=(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) X,
    @y:=(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') Y,
    ROUND((@x * 100) / @y, 2) Z
FROM
    series_tmp t

If you want to use your aliases, MySQL allows you only to use those in ORDER BY , GROUP BY and HAVING

As exampple you can use ORDER BY X DESC,Y ASC or HAVING X > 0.5

score 2 · Answer 3 · answered Sep 07 '20 at 07:54

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'

In this particular case I recommend you to use conditional aggregating:

SELECT COUNT(*) AS total_count,
       SUM(t1.some_state = 'x state') AS x_state_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

If the condition t1.some_state = 'x state' for some particular row is TRUE (which is an alias of 1 in MySQL) then SUM() will add 1 to intermediate sum for this row.

If the condition t1.some_state = 'x state' for some particular row is FALSE (which is an alias of 0 in MySQL) then SUM() will add 0 (i.e. will not alter intermediate sum) for this row.

As a result the amount of matched rows will be calculated.

In most simple cases such conditional aggregating instead of alias reuse is enough.

MySQL reuse select aliases

3 Answers3