MySQL – Calculating Differences Between Maximum and Current Values for Each Row

MySQL

My sales table looks like this.

region          sales
======================
East            1200
Midwest         (Null)
North           2500
South           4000
West            2400

Expected query result:

region          sales     diff
================================
East            1200      2800
Midwest         0         4000
North           2500      1500
South           4000      0
West            2400      1600

The diff column is calculated by max(sales) - sales. I don't know well about SQL and I even don't know how I can write a search on google. Any help will be appreciated.

Best Answer

The MAX value you get in an subselect, the rest is simple math

CREATE TABLE Sales
    (`region` varchar(7), `sales` varchar(6))
;
    
INSERT INTO Sales
    (`region`, `sales`)
VALUES
    ('East', '1200'),
    ('Midwest', '(Null)'),
    ('North', '2500'),
    ('South', '4000'),
    ('West', '2400')
;

SELECT 
`region`
, `sales`
,(SELECT MAX(`sales` ) FROM Sales) - `sales` AS diff
FROM Sales

region  | sales  | diff
:------ | :----- | ---:
East    | 1200   | 2800
Midwest | (Null) | 4000
North   | 2500   | 1500
South   | 4000   |    0
West    | 2400   | 1600

db<>fiddle here

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

Best Answer

Related Solutions

Mysql – Differences between “Unique Key” and “Primary Key”

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

PROPOSED QUERY

YOUR SAMPLE DATA

PROPOSED QUERY EXECUTED

PROPOSED QUERY EXPLAINED

Related Question