How to calculate a total by comparing current and next row I have a table with some rows:


I have a few questions to accomplish the task altogether. Is it possible to calculate total of transmitted and received based on checked_date and ip and connect_time using mysql query? If yes, can I use logical operators to calculate total of transmitted and received? To get correct total transmitted and received I need to use a formula like: 

if connect_time[i] &gt; connect_time[j] 
then total += transmitted[i]+transmitted[j] 
else total += ABS(transmitted[i]-transmitted[j]) 
end


How to get it done with this formula, does anyone have an idea? P.S. i = current row and j = next row.
Using user variables, you calculate running total for each line like a spreadsheet

PROPOSED QUERY

SET @total = 0;
SET @curr_trx = 0;
SET @curr_con = 0;
SET @lineitem = 0;
SELECT GrandTotal FROM
(
    SELECT
        (@lineitem:=@lineitem+1) line,
        (@prev_con:=@curr_con),
        (@curr_con:=connect_time),
        (@prev_trx:=@curr_trx),
        (@curr_trx:=transmitted),
        (@newvalue:=IF(@prev_con&gt;@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
        (@total:=@total+@newvalue) GrandTotal
    FROM mytable
) A WHERE line = @lineitem;


YOUR SAMPLE DATA

mysql&gt; DROP DATABASE IF EXISTS timyash;
Query OK, 1 row affected (0.22 sec)

mysql&gt; CREATE DATABASE timyash;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE timyash
Database changed
mysql&gt; CREATE TABLE mytable
    -&gt; (
    -&gt;     id INT NOT NULL AUTO_INCREMENT,
    -&gt;     transmitted INT NOT NULL,
    -&gt;     connect_time INT NOT NULL,
    -&gt;     PRIMARY KEY (id)
    -&gt; );
Query OK, 0 rows affected (0.35 sec)

mysql&gt; INSERT INTO mytable (transmitted,connect_time) VALUES
    -&gt; (2650131,117987),(6465178,78073),(25905117,159268),
    -&gt; (59178089,410282),(73502942,596408),(75695427,683045),
    -&gt; (77576167,740379);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql&gt;


PROPOSED QUERY EXECUTED

mysql&gt; SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT GrandTotal FROM
    -&gt; (
    -&gt;     SELECT
    -&gt;         (@lineitem:=@lineitem+1) line,
    -&gt;         (@prev_con:=@curr_con),
    -&gt;         (@curr_con:=connect_time),
    -&gt;         (@prev_trx:=@curr_trx),
    -&gt;         (@curr_trx:=transmitted),
    -&gt;         (@newvalue:=IF(@prev_con&gt;@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    -&gt;         (@total:=@total+@newvalue) GrandTotal
    -&gt;     FROM mytable
    -&gt; ) A WHERE line = @lineitem;
+------------+
| GrandTotal |
+------------+
|   82876429 |
+------------+
1 row in set (0.08 sec)

mysql&gt;


PROPOSED QUERY EXPLAINED

Look at the subquery

SELECT
    (@lineitem:=@lineitem+1) line,
    (@prev_con:=@curr_con),
    (@curr_con:=connect_time),
    (@prev_trx:=@curr_trx),
    (@curr_trx:=transmitted),
    (@newvalue:=IF(@prev_con&gt;@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))),
    (@total:=@total+@newvalue) GrandTotal
FROM mytable


If you run this with the variables, you see everything being added up for you line by line

mysql&gt; SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT
    -&gt;     (@lineitem:=@lineitem+1) line,
    -&gt;     (@prev_con:=@curr_con),
    -&gt;     (@curr_con:=connect_time),
    -&gt;     (@prev_trx:=@curr_trx),
    -&gt;     (@curr_trx:=transmitted),
    -&gt;     (@newvalue:=IF(@prev_con&gt;@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    -&gt;     (@total:=@total+@newvalue) GrandTotal
    -&gt; FROM mytable;
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
| line | (@prev_con:=@curr_con) | (@curr_con:=connect_time) | (@prev_trx:=@curr_trx) | (@curr_trx:=transmitted) | newval   | GrandTotal |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
|    1 |                      0 |                    117987 |                      0 |                  2650131 |  2650131 |    2650131 |
|    2 |                 117987 |                     78073 |                2650131 |                  6465178 |  9115309 |   11765440 |
|    3 |                  78073 |                    159268 |                6465178 |                 25905117 | 19439939 |   31205379 |
|    4 |                 159268 |                    410282 |               25905117 |                 59178089 | 33272972 |   64478351 |
|    5 |                 410282 |                    596408 |               59178089 |                 73502942 | 14324853 |   78803204 |
|    6 |                 596408 |                    683045 |               73502942 |                 75695427 |  2192485 |   80995689 |
|    7 |                 683045 |                    740379 |               75695427 |                 77576167 |  1880740 |   82876429 |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
7 rows in set (0.00 sec)

mysql&gt;


The proposed query has the subquery return a lineitem number. I get the last one to show the final total.

Remember the formula from your question ?

if connect_time[i] &gt; connect_time[j] 
then total += transmitted[i]+transmitted[j] 
else total += ABS(transmitted[i]-transmitted[j]) 
end


The newval column computes the formula