# Cumulative Subtraction from external input

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?

• I don't get it. Is the 20 a hard-coded value? Will it always be 20? How do you sequence the data to know which is the first, second etc? The sample table's External Value is not continuous as the description would suggest. Please edit your question to expand, explain and correct. – Michael Green Jul 9 '16 at 9:11
• @MichaelGreen First row is 20-1=19. 2nd row uses the 19, and minuses Value1=2, giving 17. Row 3 uses the 17 from row 2 and subtracts 3, giving 14.... etc etc – Philᵀᴹ Jul 9 '16 at 12:09

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);
``````