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):

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...

CodePudding user response：

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

CodePudding user response：

select sum(if(valuediff<0,-valuediff,0))
from (
    select value-lag(value) over (order by id) as valuediff
    from mysterytablename
) valuediff

CodePudding user response：

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