Mysql recursive substracting and multiplying group

Question

Couldn't really explain my problem with words, but with an example I can show it clearly:

I have a table like this:

id    num   val  flag
0     3     10   1
1     5     12   2
2     7     12   1
3     11    15   2

And I want to go through all the rows, and calculate the increase of the "num", and multiply that difference with the "val" value. And when I calculated all of these, I want to add these results together, but grouped based on the "flag" values.

This is the mathematical equation, that I want to run on the table:

Result_1 = (3-0)*10 + (7-3)*12
Result_2 = (5-0)*12 + (11-5)*15

78  = Result_1
150 = Result_2

Thank you.

Answer 1

Interesting question. Unfortunately MYSQL doesn't support recursive queries, so you'll need to be a little creative here. Something like this could work:

select flag,
  sum(calc)
from (
  select flag, 
    (num-if(@prevflag=flag,@prevnum,0))*val calc,
    @prevnum:=num prevnum,
    @prevflag:=flag prevflag
  from yourtable 
    join (select @prevnum := 0, @prevflag := 0) t
  order by flag
  ) t
group by flag

• SQL Fiddle Demo