MySQL – How to Calculate Total by Comparing Current and Next Row

MySQL

I have a table with some rows:
enter image description here

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.

Best Answer

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;

YOUR SAMPLE DATA

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

Related Solutions

MySQL – Determining Row Size in MyISAM

Yes it's the average size of each row in your table.

row size * N rows = table size

Row size = 83 B

Rows = 22,000,001

Row Size times Number of rows:

83 * 22000001 = 1826000083

Convert result in Giga Bytes:

1826000083 / 1024 / 1024 / 1024 = 1.7

Total = 1.7 GiB

Max

MySQL Query – How to Get a Sorted Result

I suppose you could do something like this from the command line/phpmyadmin:

Assuming the following table:

mysql> SELECT * FROM test;
+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  3 | free     |
|  4 | free     |
|  5 | paid     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

Make your custom ordering by selecting twice the table and using a variable as a counter:

mysql> SET @i := 0; 
SET @j := 0.5; 
SELECT id, cat_type FROM 
(SELECT @i := @i + 1 as ordering, id, cat_type FROM test WHERE cat_type = 'free' 
 UNION 
 SELECT @j := @j + 2 as ordering, id, cat_type FROM test WHERE cat_type = 'paid') 
AS base_table 
ORDER BY ordering;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  5 | paid     |
|  3 | free     |
|  4 | free     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

This is technically incorrect, as it could explore the original tables not in id order, but assuming you are using InnoDB, a full table scan in index order should be performed.

Also, this is terribly unoptimized:

+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type       | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort  |
|  2 | DERIVED           | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
|  3 | UNCACHEABLE UNION | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
| NULL | UNION RESULT      | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

To the point that if you wanted to do this in a large table, doing it programatically or using cursors would be a better idea.