CODEWITHSUNDEEP
mysql
William Falcon
William Falcon

Reputation: 9813

MYSQL Update value from the division of the result of a query

I want to update a field called ratio. For the ratio, I want to calculate it from two values that I get from this query.

/*Returns the sum of the latest two entries in two different rows*/
select sum(value) as sum from db.s s
where s.r_id = 76703
group by timeIn
order by timeIn DESC
LIMIT 2;
/*calculate ratio??*/
UPDATE db.r SET ratio = RESULT??

The query above gives two rows with two values.
Ratio should be row1/row2 Then update the ratio field on a different table.

Upvotes: 0

Views: 591

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

You could do a rank, and use CASE to get the two values;

UPDATE r SET ratio = (
  SELECT SUM(CASE WHEN c=2 THEN sum ELSE 0 END) /
         SUM(CASE WHEN c=1 THEN sum ELSE 0 END)
  FROM (
    SELECT SUM(value) sum, @c:=@c+1 c
    FROM s, (SELECT @c:=0)a
    WHERE s.r_id = 76703
    GROUP BY timeIn
    ORDER BY timeIn 
    LIMIT 2
  )z
);

An SQLfiddle to test with.

Upvotes: 0

Yigitalp Ertem
Yigitalp Ertem

Reputation: 2039

I am almost sure that this problem has a way more efficient and clean solution. But, this may also work.

UPDATE db.r SET ratio = 
(
 select sum(value) as sum from db.s s
 where s.r_id = 76703
 group by timeIn
 order by timeIn DESC
 LIMIT 1;
)
/
(
 select sum(value) as sum from db.s s
 where s.r_id = 76703
 group by timeIn
 order by timeIn DESC
 LIMIT 1,1;
)

Upvotes: 1

Related Questions