Mysql – Cumulative Subtraction from external input

MySQL

I have an external value 20. I need 1 to be subtracted from 20 (=> 19) and then 2 to be subtracted from 19 (=> 17) and so on.

Data:

Serial No | Value1 | External value(20)
       A  |     1  |                19
       B  |     2  |                17
       C  |     3  |                14
       D  |     4  |                10
       E  |     5  |                 5

How can I do it?

Best Answer

I supposed that the third column is the result of your subtraction and doesn't exist in your main table.

Query:

SELECT `Serial`, `Value1`
    , (@value := @value-Value1) `external value`
FROM `data` d
    , (SELECT @value := 20) v
ORDER BY `Serial`

The query uses a variable which is set to 20 at the beginning, cross join it to the data table and subtract each Value1 one by one. Value1 are order by Serial, from A to E. The order seems to be A to E but based on real data, you will have to order it according to your needs.

Output:

Serial | Value1 | external value
-------|--------|----------------
     A |      1 |             19
     B |      2 |             17
     C |      3 |             14
     D |      4 |             10
     E |      5 |              5

Sample (sqlfiddle):

CREATE TABLE data(Serial char, Value1 int);
INSERT INTO data(Serial, Value1) VALUES
  ('A', 1), 
  ('B', 2), 
  ('C', 3), 
  ('D', 4), 
  ('E', 5);
Related Question