Column that is the difference between values of sa

Question

My table is as follows:

Balance          Value 1        Value2          Date        Daily Change (Expected)  
8                8,648,668.60   12,620,390.51   3/27/2018     -1
7                3,087,707.30   3,494,174.70    3/28/2018     -1
6                3,039,443.51   2,345,356.15    3/29/2018     -1

How can I format a query so that the "Daily Change" column is the difference of the latter day's balance and the former's?

Lets refer to my table as "table"

In order

3/27/2018 - 3/26/2018

3/28/2018 - 3/27/2018

3/29/2018 - 3/28/2018

Answer 1

I used the following to answer this question:

SELECT t1.*,t1.balance-t2.balance Daily_Change FROM TestData t1 LEFT JOIN TestData t2 ON t2.report_date=DATE_SUB(t1.report_date, INTERVAL 1 DAY) AND t1.account=t2.account ORDER BY t1.report_date,t1.account

Answer 2

You can use a left join:

select t.*,
       (t.balance - tprev.balance) as daily_change
from `table` t left join
     `table` tprev
     on t.date = tprev.date  + interval 1 day;