CODEWITHSUNDEEP
mysqlmysql-variables
user2370579
user2370579

Reputation: 361

Define a variable within select and use it within the same select

Is there a possibility to do something like this?

SELECT 
    @z:=SUM(item),
    2*@z
FROM
    TableA;

I always get NULL for the second column. The strange thing is, that while doing something like

SELECT 
    @z:=someProcedure(item),
    2*@z
FROM
    TableA;

everything works as expected. Why?

Upvotes: 33

Views: 73291

Answers (4)

Rana Muhammad Usama
Rana Muhammad Usama

Reputation: 302

we can't safely define and use a variable in the same select statement.Best Approch is to not SET a Variable in the SELECT Query. Alternative Query is

select temp.z,temp.z*2
    from (SELECT sum(item) As `z`
          FROM TableA
         )temp;

Upvotes: 0

pala_
pala_

Reputation: 9010

mysql> select @z := sum(5), if(@z := sum(5), 2*@z, 0) ;
+--------------+------------------------------+
| @z := sum(5) | if(@z := sum(5), 2*@z, null) |
+--------------+------------------------------+
|            5 |                           10 |
+--------------+------------------------------+

I believe wrapping the 2*@z in the if statement will ensure the sum is performed BEFORE the additional calculation.

Upvotes: 1

Ravi Parekh
Ravi Parekh

Reputation: 5594

Works in mysql 5.5

select @code:=sum(2), 2*@code

+---------------+---------+
| @code:=sum(2) | 2*@code |
+---------------+---------+
|             2 |       4 |
+---------------+---------+

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269773

MySQL documentation is quite clear on this:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

You can do what you want using a subquery:

select @z, @z*2
from (SELECT @z:=sum(item)
      FROM TableA
     ) t;

Upvotes: 42

Related Questions