CODEWITHSUNDEEP
mysql
sona das
sona das

Reputation: 1439

Calculations of different columns in Mysql Query

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.

Upvotes: 2

Views: 51

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 2

Related Questions