Mysql recursive substracting and multiplying values

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
0     3     10
1     5     12
2     7     12
3     11    15

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.

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

Result = (3-0)*10 + (5-3)*12 + (7-5)*12 + (11-7)*15
138 = Result

Thank you.

DRapp · Accepted Answer · 2014-03-29 01:29:54Z

2

You can do with mysql variables, but you will still get one record for each entry.

select
      @lastTotal := @lastTotal + ( (yt.num - @lastNum) * yt.val ) thisLineTotal,
      @lastNum := yt.num as saveForNextRow,
      yt.id
   from
      yourTable yt,
      ( select @lastTotal := 0,
               @lastNum := 0 ) sqlvars
   order by
      id

This SHOULD give you what you want to confirm the calculations to each record basis.

Now, to get the one record and one column result, you can wrap it such as

select
      pq.thisLineTotal
   from
      (above entire query ) as pq
   order by 
      pq.id DESC
   limit 1

answered Mar 29, 2014 at 1:29

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bk_ Over a year ago

I was thinking: select sum((a.num - ifnull(b.num, 0)) * a.val) as result from tt a left join tt b on b.id = (a.id - 1); However this assumes id always increments by one. I like the trick of variables in a join.

fpjxmqjtaw Over a year ago

Thank you, it is working, but I've realized that I've left out a crucial part of my problem from the question, could you please look at my modified question here? : link

Ron Smith · Accepted Answer · 2014-03-29 01:50:30Z

0

Assuming the IDs are consecutive as your sample data suggests, just join the table to itself:

select sum((t1.num-ifnull(t2.num,0))*t1.val) YourValue
from YourTable t1
  left join YourTable t2
    on t2.id = t1.id - 1;

http://www.sqlfiddle.com/#!2/40b9f/12

answered Mar 29, 2014 at 1:50

Ron Smith

3,2661 gold badge15 silver badges16 bronze badges

Comments

mseifert · Accepted Answer · 2014-03-29 02:01:40Z

This will give you the total. Make sure to order in the order you wish - I have ordered by id

SET @runtot:=0;
SET @prevval:=0;

select max(rt) as total FROM (
SELECT
   q.val,
   q.num,
   (@runtot := @runtot + (q.num- @prevval) * q.val) AS rt,
   (@prevval := q.num) AS pv
FROM thetable q
ORDER by ID) tot

If you want to see the details of the calculation, leave out the outer select as so:

SET @runtot:=0;
SET @prevval:=0;

SELECT
   q.val,
   q.num,
   (@runtot := @runtot + (q.num- @prevval) * q.val) AS rt,
   (@prevval := q.num) AS pv
FROM thetable q
ORDER by ID

If it is possible to have negative numbers for your column values, using max(rt) won't work for the total. You should then use:

SET @runtot:=0;
SET @prevval:=0;

select @runtot as total FROM (
SELECT
   q.val,
   q.num,
   (@runtot := @runtot + (q.num- @prevval) * q.val) AS rt,
   (@prevval := q.num) AS pv
    FROM thetable q
ORDER by ID) tot LIMIT 1

Collectives™ on Stack Overflow

Mysql recursive substracting and multiplying values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related