How to add subtotal to a table column displaying NULL in MySQL?


Let us first create a table −

mysql> create table DemoTable
-> (
-> Amount int,
-> SubTotal int
-> );
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

ysql> insert into DemoTable(Amount) values(50);
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable(Amount) values(60);
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable(Amount) values(70);
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable(Amount) values(80);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+----------+
| Amount | SubTotal |
+--------+----------+
|     50 |   NULL |
|     60 |   NULL |
|     70 |   NULL |
|     80 |   NULL |
+--------+----------+
4 rows in set (0.00 sec)

Here is the query to add subtotal to a column in MySQL.

mysql> set @sum := 0;
Query OK, 0 rows affected (0.10 sec)

mysql> update DemoTable set SubTotal = (@sum := @sum + Amount);
Query OK, 4 rows affected (0.19 sec)
Rows matched: 4  Changed: 4 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+--------+----------+
| Amount | SubTotal |
+--------+----------+
|     50 |     50   |
|     60 |     110  |
|     70 |     180  |
|     80 |     260  |
+--------+----------+
4 rows in set (0.00 sec)
raja
Published on 11-Jul-2019 11:47:07
Advertisements