Calculations of different columns in Mysql Query

Question

I have a Table:-

+-----+--------------+--------------+----------+--------------------+---------------+-----------------+
| id  | CustomerName | VideoQuality | IsActive | BufferedTime       | ElapsedTime   | TotalBufferTime |
+-----+--------------+--------------+----------+--------------------+---------------+-----------------+
| 139 | HotStar      | 180          | Yes      | 10.367167126617211 | 30.000000000  | NULL            |
| 140 | HotStar      | 1300         | NULL     | 5.43524230876729   | 34.000000000  | NULL            |
| 141 | HotStar      | 1300         | NULL     | 5.671054515212042  | 38.000000000  | NULL            |
| 142 | HotStar      | 1300         | NULL     | 5.045639532902047  | 41.000000000  | NULL            |
| 143 | HotStar      | 1300         | NULL     | 5.455747718023355  | 44.000000000  | NULL            |
| 144 | HotStar      | 1300         | NULL     | 5.691559924468107  | 49.000000000  | NULL            |

i want to calculate the columns BufferTime and ElapsedTime and insert that output to TotalBufferTime column but i want to skip the first row of the BufferTime. So the fisrt calculation will be 5.43 + 30.000 second calculation will be 5.67 + 34.00 and so on. I also have a column IsActive which shows the first row of Buffer time.

I want to do something like this :-

update RequestInfo SET `TotalBufferTime` = BufferedTime + ElapsedTime;

only thing i want to skip only the first row of the column buffered time.

You also need a field that determines row order. There is no inherent row order in SQL tables. — Giorgos Betsos
– Giorgos Betsos, Commented Jan 16, 2017 at 11:24
@sonadas can you give sqlfiddle so I can give answer easily as per your requirements. — Sadikhasan
– Sadikhasan, Commented Jan 16, 2017 at 11:32

Giorgos Betsos · Accepted Answer · 2017-01-16 13:49:52Z

2

Assuming you a field id that determines row order in your table, you can use a correlated subquery so as to get BufferedTime of previous row like this:

SELECT  t1.CustomerName, t1.VideoQuality, t1.IsActive, t1.BufferedTime,
        t1.ElapsedTime, 
        (SELECT t2.BufferedTime
         FROM mytable AS t2
         WHERE t2.td > t1.id
         ORDER BY id LIMIT 1) + t1.ElapsedTime AS TotalBufferTime
FROM mytable AS t1
WHERE IsActive IS NULL

Edit:

To UPDATE you can use the following query:

SET @et = 0;
SET @ElapsedTime = NULL;

UPDATE RequestInfo 
SET TotalBufferTime = CASE 
                         WHEN (@et := @ElapsedTime) < 0 THEN NULL
                         WHEN @ElapsedTime := ElapsedTime THEN BufferedTime + @et
                      END   
ORDER BY id;

The trick here is to use a CASE expression where the first WHEN clause is always evaluated (because it is the first one) but is never true. This way @et variable is initialized with the value of @ElapsedTime, i.e. the value of the previous record.

Demo here

edited Jan 16, 2017 at 13:49

answered Jan 16, 2017 at 11:31

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

2 Comments

sona das Over a year ago

i m getting error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS TotalBufferTime FROM RequestInfo AS t1 WHERE IsActive IS NULL' at line 6

Giorgos Betsos Over a year ago

@sonadas There was one parentheses too much. Please check the edit I made.

Collectives™ on Stack Overflow

Calculations of different columns in Mysql Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related