MySQL - Calculate the difference between rows

Question

So I'm working with MySQL and PHP. Having some trouble trying to get a query that calculates the difference between consecutive rows.

What I need is to get the difference between each row for: data_views, data_sub & data_videos for each channel URL.

Much appreciated if anyone can help :)

Here's my table structure:

Have you tried anything or just thought asking help for your assignment ? — Ash, May 02 '16 at 03:53
So you want to calculate the difference between the ids 1-3, 3-4, 4-5 and so on? — Qirel, May 02 '16 at 03:53
Can you just put these values in arrays and then perform the subtraction on the array values? — stackunderflow, May 02 '16 at 03:56
@Qirel It's for each channel_url (so... WHERE channel_url = '...') — Harvey Connor, May 02 '16 at 04:16
select (data_views-data_sub-data_videos) AS result from `your table` — Sanooj T, May 02 '16 at 04:56

Mohammedshafeek C S · Accepted Answer · 2016-05-02T09:19:25.757

Try this..if you don't want ordering just remove ORDER BY A.channel_url clause..

SELECT 
  A.*,
  A.data_views - IFNULL(B.data_views, A.data_views) AS data_views_diff,
  A.data_subs - IFNULL(B.data_subs, A.data_subs) AS data_subs_diff,
  A.data_videos - IFNULL(B.data_videos, A.data_videos) AS data_videos_diff 
FROM
  my_table A 
  LEFT JOIN my_table B 
    ON B.id = 
    (SELECT 
      MIN(id) 
    FROM
      my_table C 
    WHERE C.id > A.id 
      AND C.id != A.id 
      AND C.channel_url = A.channel_url) 
ORDER BY A.channel_url;

Check Fiddle

MySQL - Calculate the difference between rows

1 Answers1