mysql - calculate the differences between two rows in SQL

Question

I have a SQL table, one row is the revenue in the specific day, and I want to add a new column in the table, the value is the incremental (could be positive or negative) revenue between a specific day and the previous day, and wondering how to implement by SQL?

Here is an example,

original table,

...
Day1 100
Day2 200
Day3 150
...

new table (add incremental column at the end, and for first column, could assign zero),

Day1 100 0
Day2 200 100
Day3 150 -50

I am using MySQL/MySQL Workbench.

thanks in advance, Lin

Answer 1

If you're okay with re-ordering the columns slightly, something like this is pretty simple to understand:

SET @prev := 0;
SELECT day, revenue - @prev AS diff, @prev := revenue AS revenue
FROM revenue ORDER BY day ASC;

The trick is that we calculate the difference to the previous first, then set the previous to the current and display it as the current in one step.

Note, this depends on the order being correct since the calculations are done during the returning of the rows, so you need to make sure you have an ORDER BY clause that returns the days in the correct order.