Question 113222 in Mysql Question posted in
The official MySQL documentation can be found here.

Get the Sum of negative differences between each row in MySQL

How do I calculate the negative difference between each row and return as a total sum?

For example (col "fictional difference" is only for better understanding):

id value fictional difference
0 63 0 -> ignrore
1 61 -2
2 55 -6
3 62 +7 -> ignore
4 57 -5
5 71 +14 -> ignore

The goal would be the absolute sum of all negative differences: 13.

Has anybody an idea how to achieve this?
I’ve tried this https://stackoverflow.com/a/29779698/12350648 but it doesn’t work for my problem…

Tags:

3

Answers


  1. 0

    I’m not saying this is the best solution, but I took the example which didn’t work for you and rebuild it slightly into this:

    SELECT SUM(IF(`diff` >= 0, 0, `diff`)) FROM (
    SELECT 
        IF(@prev IS NULL, 0, @diff := `value` - @prev) AS `diff`,
        @prev := `value`
    FROM table1
    ORDER BY id
) AS `diffTable`

    The inner SELECT builds a table with the difference called diffTable and then the outer SELECT sums the negative results.

    See: http://sqlfiddle.com/#!9/62bc4d/12

    Login or Signup to reply.
  2. 0 
    select sum(if(valuediff<0,-valuediff,0))
from (
    select value-lag(value) over (order by id) as valuediff
    from mysterytablename
) valuediff
    Login or Signup to reply.
  3. 0

    I’m using a table join to achieve this.

    select abs(sum(diff)) as abs_diff
from (select if(t2.value-t1.value<0,t2.value-t1.value,0) as diff 
    from test t1
    join test t2
    on t2.id=t1.id+1) t
;
-- result set:
13
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search