CODEWITHSUNDEEP
mysqlsqljoin
Don P
Don P

Reputation: 63597

SQL get change in values based on consecutive dates, grouped by a value

I want to get the change in Price from day to day. What SQL query will accomplish this?

Original Table

Date       Company    Price
---------------------------
1/4/2012   Apple      458
1/3/2012   Apple      462
1/2/2012   Apple      451
1/1/2012   Apple      450

1/4/2012   Google     553
1/3/2012   Google     541
1/2/2012   Google     535
1/1/2012   Google     531

Desired Table

Date       Company    Price   Day_Change
-------------------------------------
1/4/2012   Apple      458     -4    
1/3/2012   Apple      462     9
1/2/2012   Apple      451     1
1/1/2012   Apple      450     NULL

1/4/2012   Google     553     13
1/3/2012   Google     541     6
1/2/2012   Google     535     4
1/1/2012   Google     531     NULL

Upvotes: 0

Views: 176

Answers (1)

dfb
dfb

Reputation: 13289

Join the table to itself on the day before and the company

SELECT t1.price-t2.price FROM
Table t1
INNER JOIN Table t2 on date_add(t1.date,INTERVAL 1 DAY) =t2.date  
       AND t1.company=t2.company

Upvotes: 1

Related Questions