assigning mysql value to variable inline

Question

Why doesn't this work i'm trying to get the previous and current value to calculate percent change. I get both values correctly but now how can I reuse them to do the math operatio

When I try the below command I get ERROR 1054 (42S22): Unknown column 'currentVal' in 'field list'

            SELECT IFNULL(DValue,0) as currentVal, 
                      (SELECT IFNULL(DValue,0) 
                       FROM ...
                       WHERE...) as previousVal, 
                      (currentVal-previousVal)/previousVal
            FROM ...
            WHERE ...;

score 2 · Answer 1 · answered Apr 11 '11 at 15:16

you can't reference an aliased column in the same SELECT, you have to put it in a subquery:

SELECT currentVal, previousVal, (currentVal-previousVal)/previousVal
FROM (
            SELECT    IFNULL(DValue,0) as currentVal, 
                      (SELECT IFNULL(DValue,0) 
                       FROM ...
                       WHERE...) as previousVal, 
            FROM ...
            WHERE ...) T;

score 1 · Accepted Answer · answered Apr 11 '11 at 15:15

Wrap another query around what you currently have and calculate your percentage there:

SELECT currentVal, previousVal, 
       (currentVal-previousVal)/previousVal AS percentChange
    FROM (SELECT IFNULL(DValue,0) as currentVal, 
                  (SELECT IFNULL(DValue,0) 
                       FROM ...
                       WHERE...) as previousVal
              FROM ...
              WHERE ...) t

assigning mysql value to variable inline

2 Answers2

Linked