Calculate delta(difference of current and previous row) in sql
Here's your original query modified accordingly:
select tt1.id, tt1.amount, tt1.AccountId, (tt1.amount-ifnull(tt2.amount, 0)) as deltafrom trans tt1 left outer JOIN trans tt2 on tt1.accountid = tt2.accountid and month(tt1.date1)-month(tt2.date1)=1;The month comparison is moved from where to on, which makes a difference for left join, and tt2.amount is replaced with ifnull(tt2.amount, 0).
The UPDATE version of the script:
update tt1set delta = (tt1.amount-ifnull(tt2.amount, 0))from trans tt1 left outer JOIN trans tt2 on tt1.accountid = tt2.accountid and month(tt1.date1)-month(tt2.date1)=1;The correct MySQL syntax for the above update should actually be:
update trans tt1 left outer JOIN trans tt2 on tt1.accountid = tt2.accountid and month(tt1.date1)-month(tt2.date1)=1 set tt1.delta = (tt1.amount-ifnull(tt2.amount, 0));(Thanks @pinkb.)