How to get sum of differences of records through mysql query

Question

I have a table as below

Value          Count
value1         55
value2         60
value3         65
value4         20
value5         25
value6         45
value7          5
value8         25

I need a query which takes the difference between two successive counts and sums them up. One condition is, if the next value is less than the previous value, then the next value is considered instead of the difference. The working is as below

Value          Count       Diff         Explanation
value1         55                       No diff as there is no previous value
value2         60            5          (60-55)
value3         75           15          (75-60) 
value4         20           20          (20 as 20 < 75)
value5         25            5          (25-20)
value6         45           20          (45-25)
value7          5            5          (5 as 5 < 45)
value8         25           20          (25-5)

The query should give the total of all the differences which is 5+15+20+5+20+5+20 = 90

Thank you in advance for the wonderful query you are going to craft for me :-)

have you tried anything besides posting question on SO for us to craft the query for you? — Ejaz, Apr 19 '13 at 08:10
To oversimplify, rows in RDBMS tables have no 'order', so you need some way of establishing one. This would normally be with a sequential (and/or incrementing) id or some kind of temporal column. A PRIMARY KEY is also required, although this sequencing column can be designated as (a component of) such. — Strawberry, Apr 19 '13 at 08:55
Hi Ejay - Yes, I posted this only after trying, but I accept that I am a very beginner. — user2298269, Apr 19 '13 at 09:11
Strawberry - Thank you for your comment. All that stuff is there, just wanted to make it simple to understand. — user2298269, Apr 19 '13 at 09:12

score 2 · Answer 1 · answered Apr 19 '13 at 08:48

You can use this query, assuming you have a table tbl(v,c) - v is the value field and c is count field.

SELECT t1.v, t1.c as cnt1,
    case 
      when t2.c is null then 0
      when t2.c > t1.c then t1.c
      else t1.c - t2.c
    end as diff
FROM TBL T1
left outer JOIN TBL T2 ON T1.v > T2.v
where not exists (
   SELECT * 
   FROM TBL T3 WHERE T3.V < T1.V AND T3.V > T2.V
)

Btw in your result value3 should be 65 and not 75

I made a Fiddle to test it

Thank you agim, for the answer and for the correction as well. — user2298269, Apr 19 '13 at 09:17

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

you can use coalesce to solve this problem

select a.Value,a.count
coalesce(a.count- 
    (select b.countfrom table_name b where b.count= a.count+ 1), a.count) as diff
from table_name  a

But this solves half of your problem.I dont know answer for second question

see here

edited May 23 '17 at 12:16

Community

1
1

answered Apr 19 '13 at 08:13

PSR

39,804
41
111
151

Thank you for your response. I am in dire need the complete solution. – user2298269 Apr 19 '13 at 08:44

How to get sum of differences of records through mysql query

2 Answers2