CODEWITHSUNDEEP
mysqlsql
Elyrion
Elyrion

Reputation: 289

SQL - Compute difference between two rows and put in another column

I have an MySQL table that contains a date, a source, a number of e.g. facebook fans and a third column, which should contain the change to the previous day.

The table looks like this:

Date        |  Source  | fans | new fans
2013-02-02  | Facebook | 100  | -
2013-02-02  | Twitter  | 60   | -
2013-02-03  | Facebook | 120  | -
2013-02-03  | Twitter  | 62   | -
2013-02-04  | Twitter  | 65   | -
2013-02-04  | Facebook | 130  | -
2013-02-05  | Facebook | 145  | -
2013-02-05  | Twitter  | 70   | -

and

the sql query should turn it into this:

Date        |  Source  | fans | new fans
2013-02-02  | Facebook | 100  | - 
2013-02-02  | Twitter  | 60   | - 
2013-02-03  | Facebook | 120  | 20
2013-02-03  | Twitter  | 62   | 2 
2013-02-04  | Twitter  | 65   | 3 
2013-02-04  | Facebook | 130  | 10
2013-02-05  | Facebook | 145  | 15
2013-02-05  | Twitter  | 70   | 5

How can I compute the difference between two days for a source?

Upvotes: 1

Views: 2029

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

You can do it using an UPDATE JOIN; a and b being the two rows to calculate the difference between, and c being LEFT joined in to make sure there's no row between a and b.

UPDATE TableA AS a
JOIN TableA b 
  ON a.`Source` = b.`Source` AND a.`Date` > b.`Date`
LEFT JOIN TableA c
  ON a.`Source` = c.`Source` AND a.`Date` > c.`Date` AND c.`Date` > b.`Date`
SET a.`new fans` = a.`fans` - b.`fans`
WHERE c.`Date` IS NULL

An SQLfiddle for testing

Note that this will UPDATE THE DATA IN YOUR TABLE and you should always back up before running SQL updates from random people on the Internet :)

Upvotes: 2

Related Questions