Find the difference between the numbers line by line mysql

Question

I am new to working with ِmysql.

i have somne data like :

Id , Date  , RegNo , CountInvestmentUnits , Subscription
 1 , 10-19 ,    12 ,                  155 ,           11
 2 , 10-20 ,    12 ,                  166 ,           12
 2 , 10-21 ,    12 ,                  190 ,           18

I want to show the result as follows:

Id, Date, RegNo, CountInvestmentUnits, Subscription, diff_CountInvestmentUnits, multiplication

1, 10-19, 12,    155,                  10,           155,                    1550
2, 10-20, 12,    166,                  12,           11,                      120
2, 10-21, 12,    190,                  18,           24,                      432

Calculations per day: The CountInvestmentUnits column is subtracted from the day before and multiplied by the column Subscription

diff_CountInvestmentUnits = CountInvestmentUnits - last day CountInvestmentUnits

multiplication = diff_CountInvestmentUnits * Subscription

Just to observe, by convention, a column called 'id' is normally a surrogate PRIMARY KEY — Strawberry, Jan 21 '21 at 13:49
here is the similar question about how to get previous row value in mysql 5 https://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql ```SELECT current_row.row, current_row.id, previous_row.row, previous_row.id FROM ( SELECT @rownum:=@rownum+1 row, a.* FROM articles a, (SELECT @rownum:=0) r ORDER BY date, id ) as current_row LEFT JOIN ( SELECT @rownum2:=@rownum2+1 row, a.* FROM articles a, (SELECT @rownum2:=0) r ORDER BY date, id ) as previous_row ON (current_row.id = previous_row.id) AND (current_row.row = previous_row.row - 1) ``` — Ayrat, Jan 21 '21 at 14:36

score 0 · Answer 1 · answered Jan 21 '21 at 13:35

You can use the LAG() analytical function. For example:

select *,
  coalesce(CountInvestmentUnits - lag(CountInvestmentUnits) over(order by date), 0)
    as diff_CountInvestmentUnits
  coalesce(CountInvestmentUnits - lag(CountInvestmentUnits) over(order by date), 0)
    * Subscription as multiplication
from t

Find the difference between the numbers line by line mysql

1 Answers1