Category:Pro Evolution SoccerQ:
SQL: Subtract value based on another column value
I am trying to subtract a value based on a column value. If a value is same, take the difference of two rows, if not, take the difference of that column and the next row.
So, if I had data like this
Date, ID, Value
10/1/2017, 1, 1
10/1/2017, 1, 1
10/1/2017, 2, 0
10/1/2017, 2, 2
10/1/2017, 3, 5
10/1/2017, 4, 3
10/2/2017, 1, 1
10/2/2017, 1, 1
10/2/2017, 2, 3
10/2/2017, 2, 3
10/2/2017, 3, 6
10/2/2017, 3, 7
10/2/2017, 4, 1
10/2/2017, 4, 5
I would end up with data like this
Date, ID, Value
10/1/2017, 1, -1
10/1/2017, 2, 1
10/1/2017, 3, 5
10/1/2017, 4, 3
10/2/2017, 1, -1
10/2/2017, 2, 3
10/2/2017, 3, 6
10/2/2017, 4, 5
I can find the difference of the next row. I am trying to find the difference if the current row and the next row are the same.
I am trying to find the difference of the value when the ID is same and subtract it from the next value but I am not quite sure how to do this with an SQL statement. Any help would be greatly appreciated.
A:
I think this is what you are looking for:
SELECT
Date,
ID,
Value,
Value - (
SELECT
Value
FROM
MyTable
WHERE
ID = MyTable.ID - 1 be359ba680
Related links: