CODEWITHSUNDEEP
mysqlsql
TadejZeleznik
TadejZeleznik

Reputation: 56

MySQL: refer to SELECT subquery alias in WHERE

Is there an easier way to write an SQL query if I want to refer to SELECT subquery in WHERE part of the query?

This is my code:

SELECT
    SUM(x) AS test,
    (SELECT SUM(y) FROM other_table) AS sub_test,
    (test + sub_test) AS total_sum
FROM my_database
WHERE
    test <> 0 AND sub_test <> 0

The code is just for illustration :) It works if I put the subquery instead of alias like:

SELECT
    SUM(x) AS test,
    (SELECT SUM(y) FROM other_table) AS sub_test,
    (SUM(x) + (SELECT SUM(y) FROM other_table)) AS total_sum
FROM my_database
WHERE
    SUM(x) <> 0 AND (SELECT SUM(y) FROM other_table) <> 0

But writing the whole subquery xx times is a bit awkward since the subquery is not just a plain "SELECT (SUM(..))"... If I want to change something in subquery I have to change it xx times and hope that I changed all of them..

Upvotes: 2

Views: 160

Answers (2)

Arion
Arion

Reputation: 31239

You could also do this:

SELECT
    *
FROM
(
    SELECT
        SUM(x) AS test,
        (SELECT SUM(y) FROM other_table) AS sub_test,
        (SUM(x) + (SELECT SUM(y) FROM other_table)) AS total_sum
    FROM my_database
) AS tbl
WHERE tbl.test <> 0 AND tbl.total_sum <> 0

Or you can also do this:

SELECT
    tbl.test,
    tbl.sub_test,
    (tbl.sub_test+tbl.test) AS total_sum
FROM
(
    SELECT
        SUM(x) AS test,
        (SELECT SUM(y) FROM other_table) AS sub_test
    FROM my_database
) AS tbl
WHERE tbl.test <> 0 AND (tbl.sub_test+tbl.test) <> 0

Upvotes: 2

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Use HAVING clause over WHERE. And
Use user variables on the values just computed on the precious column expressions. 

SELECT
    @sx:=SUM(x) AS test,
    (SELECT @sy:=SUM(y) FROM other_table) AS sub_test,
    (@sx + @sy) AS total_sum
FROM my_database
HAVING
    -- SUM(x) <> 0 AND (SELECT SUM(y) FROM other_table) <> 0
    test <> 0 AND sub_test <> 0

Upvotes: 3

Related Questions