Comparing 2 MySQL rows and calculating the delta

Question

I have a table with some statistics, that looks like this after selecting and grouping:

SELECT * FROM Statistics WHERE id IN (
    select max(id) id
    from Statistics
    group by DAY(Statistics.Datetime_Scan)
)

ID      Datetime_Scan   Count
2       4/6/2017        302
10      4/7/2017        391
18      4/8/2017        500
26      4/9/2017        605
34      4/10/2017       725
42      4/11/2017       832

I would like to add a new column with the delta number, that will show the daily increase/decrease in Count. Like the table below:

ID      Datetime_Scan   Count   Delta
2       4/6/2017        302     0
10      4/7/2017        391     89
18      4/8/2017        500     109
[...]

Should I do a kind of loop in MySQL and calculate the delta with the previous day from each date? Thanks.

Possible duplicate of [MySQL difference between two rows of a SELECT Statement](http://stackoverflow.com/questions/14857159/mysql-difference-between-two-rows-of-a-select-statement) — Skgland, Apr 18 '17 at 21:59
have you tried the analytic function lead and lag. this should help you to retrieve data from next record. — Abs, Apr 18 '17 at 22:08

score 1 · Accepted Answer · answered Apr 18 '17 at 22:21

You can get it using a variable.

drop table if exists Statistics;
create table if not exists Statistics (ID int, Datetime_Scan date, Count int);
insert into Statistics values
( 2, '2017/4/6',        302),
(10, '2017/4/7',        391),
(18, '2017/4/8',        500),
(26, '2017/4/9',        605),
(34, '2017/4/10',       725),
(42, '2017/4/11',       832);

select Id, Datetime_Scan, Count, @last_count, 
       if (@last_count = 0, 0, Count - @last_count) as Delta,
       @last_count := Count
from
     (select @last_count := 0) x,
     (select id, Datetime_Scan, Count 
      from Statistics
      order by id, datetime_scan) y
order by id, datetime_scan;

drop table if exists Statistics;

| Id | Datetime_Scan       | Count | @last_count | Delta | @last_count := Count |
|----|---------------------|-------|-------------|-------|----------------------|
| 2  | 06.04.2017 00:00:00 | 302   | 0           | 0     | 302                  |
| 10 | 07.04.2017 00:00:00 | 391   | 302         | 89    | 391                  |
| 18 | 08.04.2017 00:00:00 | 500   | 391         | 109   | 500                  |
| 26 | 09.04.2017 00:00:00 | 605   | 500         | 105   | 605                  |
| 34 | 10.04.2017 00:00:00 | 725   | 605         | 120   | 725                  |
| 42 | 11.04.2017 00:00:00 | 832   | 725         | 107   | 832                  |

Rextester here: http://rextester.com/KOHDW53168

Comparing 2 MySQL rows and calculating the delta

1 Answers1