CODEWITHSUNDEEP
mysqlsql
Lakmal Premaratne
Lakmal Premaratne

Reputation: 1229

mysql obtain running difference

I have below table structure and data:

-----------------------------
|  Date      | Accu. Output |
-----------------------------
| 2017-01-01 |          150 |
| 2017-01-02 |          165 |
| 2017-01-03 |          182 |
| 2017-01-04 |          190 |
| 2017-01-05 |          210 |
-----------------------------

I need to generate below output from above dataset. I can do a client side processing. But I am trying to write a query to get this.

----------------------------------------------
|  Date      |   Accu. Output | Daily Output |
----------------------------------------------
| 2017-01-05 |            210 |           20 |
| 2017-01-04 |            190 |            8 |
| 2017-01-03 |            182 |           17 |
| 2017-01-02 |            165 |           15 |
| 2017-01-01 |            150 |            0 |
----------------------------------------------

Daily output is the difference between current Accu. Output and previous day's Accu. Output.

Thanks

Upvotes: 2

Views: 582

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Here is a method uses a left join:

select t.*,
       coalesce(t.accu_output - tprev.accu_output, 0) as diff
from t left join
     t tprev
     on tprev.date = t.date - interval 1 day;

This assumes -- as in your sample data -- that the days are increasing with no gaps and exactly one value per day. These conditions are implied by your question (previous day's output).

Upvotes: 4

Related Questions