Subtract two rows' values within the same column using Mysql group by ID

Question

I have a table as shown below. I want to do a partition and then subtract the values in the same column to get the difference.

Since there is no partition or equivalent function available in MySQL, can anyone give me an idea of how to do it? I have worked out the partition but not the other part.

SELECT ID,Date,
       @row_number:=CASE WHEN @ID=ID THEN @row_number+1 ELSE 1 END AS row_number,
       @ID:=ID AS ID,value
FROM table1, (SELECT @row_number:=0,@ID:='') AS t 
ORDER BY id,Date;

Input:

 ID           Date        Value
1001    24-07-2017 09:43    10
1002    24-07-2017 09:43    11
1003    22-08-2017 21:42    10
1001    07-09-2017 20:33    11
1003    07-09-2017 20:33    44
1002    24-07-2017 09:43    55
1004    07-09-2017 20:33    66

Output should be:

rowno  ID         Date        Value Diff
1     1001  24-07-2017 09:43    10  N/A
2     1001  07-09-2017 20:33    11  1
1     1002  24-07-2017 09:43    11  N/A
2     1002  24-07-2017 09:43    55  44
1     1003  22-08-2017 21:42    10  n/A
2     1003  07-09-2017 20:33    44  34
1     1004  07-09-2017 20:33    66  N/A

Answer 1

Your dates are incorrect. When you order by this column, you order by day first. Proper ordering is year-month-day. Use the proper datatype, datetime or timestamp. To do this you can do the following:

alter table table1 add column d datetime;
update table1 set d = str_to_date(`Date`, '%d-%m-%Y %H:%i');
alter table table1 drop column `Date`;

The query to get your desired result is then:

SELECT ID, d, Value,
       IF(@ID=ID, Value - @prevV, 'N/A') AS diff,
       @row_number:=CASE WHEN @ID=ID THEN @row_number+1 ELSE 1 END AS row_number,

       @prevV := Value,
       @ID:=ID AS ID
FROM table1
, (SELECT @row_number:=0, @ID:='', @prevV := NULL) AS t 
ORDER BY id, d;

All you have to do is add another variable to hold the value of the previous row.

• see it working live in an sqlfiddle
• here's another sqlfiddle to showcase what happens when you have 3 rows per ID