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

SELECT a.day, a.revenue , a.revenue-COALESCE(b.revenue,0) as previous_day_rev 
FROM DailyRevenue a 
LEFT JOIN DailyRevenue b on a.day=b.day-1

the query assume that each day has one record in the table. If there could be more than 1 row for each day you need to create a view that sums up all days grouping by day.