# 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] > 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`.

• What does next row mean, next checked_date? Dec 29, 2014 at 12:46
• @Lennart, both (next checked_date and next connect_time) Dec 29, 2014 at 12:50
• Not sure I follow, do you mean `order by checked_date, connect_time`? I.e. in case of a draw for checked_date, use connect_time as a secondary sort criteria? Dec 29, 2014 at 12:54
• @Lennart, sorry, I need the total for transmitted and received based on connect_time like if current connect_time is > next connect_time then sum(current transmitted + next transmitted) else sum(abs(current transmitted - next transmitted))) where checked_date between checked_date. Dec 29, 2014 at 13:01

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>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
(@total:=@total+@newvalue) GrandTotal
FROM mytable
) A WHERE line = @lineitem;
``````

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

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

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

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

mysql> 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>
``````

# PROPOSED QUERY EXECUTED

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

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

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

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

mysql> 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>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
->         (@total:=@total+@newvalue) GrandTotal
->     FROM mytable
-> ) A WHERE line = @lineitem;
+------------+
| GrandTotal |
+------------+
|   82876429 |
+------------+
1 row in set (0.08 sec)

mysql>
``````

# 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>@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> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> SELECT
->     (@lineitem:=@lineitem+1) line,
->     (@prev_con:=@curr_con),
->     (@curr_con:=connect_time),
->     (@prev_trx:=@curr_trx),
->     (@curr_trx:=transmitted),
->     (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
->     (@total:=@total+@newvalue) GrandTotal
-> 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>
``````

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] > connect_time[j]
then total += transmitted[i]+transmitted[j]
else total += ABS(transmitted[i]-transmitted[j])
end
``````

The newval column computes the formula

• @RolandoMySQLDBA, shouldn't there be an order by in the query? Dec 29, 2014 at 16:14
• @Lennart the query for the sample table I made is ordered by PRIMARY KEY. If I made the sample table exactly as the question has it, the subquery would have `FROM mytable ORDER BY checked_date`. If there is a WHERE clause on the range of check_date, the query would have `FROM mytable WHERE checked_date>='2014-12-10' ORDER BY checked_date` Dec 29, 2014 at 16:17

Still not sure I understand what ordering operator to use, but I assume it is checked_date. I also assume that (ip, mac, checked_date) is unique. This would be almost trivial in a DBMS that support window functions, unfortenately MySQL does not. Here's one suggestion (untested, it really helps if you provide sample data instead of an image) to get the data you need for your calculations.

``````select x.checked_date, x.ip, x.mac, x.transmitted, x.received, x.connect_time
from T as x
join T as y
on y.ip = x.ip
and y.mac = x.mac
and y.checked_date = ( select min(z.checked_date)
from T z
where z.ip = x.ip
and z.mac = x.mac
and z.checked_date > x.checked_date )
``````

The result for the row can be calculated as:

``````case when x.connect_time > y.connect_time
then x.transmitted + y.transmitted
else abs(x.transmitted - y.transmitted)
end
``````

To get the running total you need to sum this expression for all rows less than or equal to the current row. Probably easiest to create a view as:

``````create view V as
select x.checked_date, x.ip, x.mac
, case when x.connect_time > y.connect_time
then x.transmitted + y.transmitted
else abs(x.transmitted - y.transmitted)
end as total
from T as x
join T as y
on y.ip = x.ip
and y.mac = x.mac
and y.checked_date = ( select min(z.checked_date)
from T z
where z.ip = x.ip
and z.mac = x.mac
and z.checked_date > x.checked_date )
``````

and then sum up the total as:

``````select v1.checked_date, v1.ip, v1.mac, sum(v2.total)
from v as v1
join v as v2
on v1.ip = v2.ip
and v1.mac = v2.mac
and v1.checked_date >= v2.checked_date
group by v1.checked_date, v1.ip, v1.mac
``````
• Thank you! But when I'm trying to create view with calculation logic, the database just freezes, by the way i've tried manual min date and its shows null Dec 29, 2014 at 15:42