2

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

2
+25

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
3

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

2
  • Thanks nbk, I just moved the whole query to a derived table and from there I'm able to use the aliased subqueries. Any difference with the variables approach? Sep 1 '20 at 12:36
  • 1
    you can't use the aliases at that position, user defined variables are different because they are set from top to bottom and can be used after being set, like displayed in my example code
    – nbk
    Sep 1 '20 at 13:03
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.