Geek asks and answers

Assign mysql value to inline variable

Why this work does not work, I am trying to get the previous and current value to calculate the percentage change. I understand both values ​​correctly, but now, how can I reuse them to perform a mathematical operation

When I try to execute the command below, I get ERROR 1054 (42S22): Unknown column "currentVal" in the "list of fields"

            SELECT IFNULL(DValue,0) as currentVal, 
                      (SELECT IFNULL(DValue,0) 
                       FROM ...
                       WHERE...) as previousVal, 
                      (currentVal-previousVal)/previousVal
            FROM ...
            WHERE ...;
+1
source share
2 answers

Wrap another query around what you have and calculate its percentage:

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
+1
source

you cannot reference columns with an alias in the same SELECT, you must 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;
+2
source

More articles:


All Articles