CODEWITHSUNDEEP
mysql
bech64
bech64

Reputation: 129

compare a new value against previous value

I would like to be able to compare a newly inserted value against a previous value having the same id, and display the difference is the percentage of change.

Example:

id | bline_id |  avg   |    date

1  |     1    | 36.500 | 2013-03-28 11:08:10

31 |     1    | 37.130 | 2013-03-29 10:37:11

So what I'm looking for is 37.130-36.5/36.5 * 100 = 1.73% change

Upvotes: 1

Views: 252

Answers (2)

Jakob Pogulis
Jakob Pogulis

Reputation: 1210

By using a self-join on the MySQL table you'll be able to calculate all possible combinations of changes, in order to avoid duplicates you'll need a greater than/smaller than [not gt/lt or equal]. The below code should give some hints on how to construct such a query, although it is not tested and might contain an error or two.

SELECT ((larger.avg - smaller.avg) / (smaller.avg * 100)) AS change 
  FROM `table` AS smaller JOIN `table` AS larger 
  WHERE larger.id > smaller.id AND larger.bline_id = smaller.bline_id;

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

e.g.:

SELECT a.*
     , ROUND(((a.avg-b.avg)/b.avg)*100,2) pct_change
  FROM
     ( SELECT x.*
            , COUNT(*) rank
         FROM test x 
         JOIN test y 
           ON y.bline_id = x.bline_id 
          AND y.id >= x.id 
        GROUP 
           BY x.id
     ) a
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM test x 
         JOIN test y 
           ON y.bline_id = x.bline_id 
          AND y.id >= x.id 
        GROUP 
           BY x.id
     ) b
    ON b.bline_id = a.bline_id
   AND b.rank = a.rank+1
 WHERE a.rank = 1;

Upvotes: 1

Related Questions