How to create cumulative sum column in MySQL?

To create a cumulative sum column in MySQL, you need to create a variable and set the value to 0. The cumulative sum increments from the current value to the next value.

First, you need to create a variable with the help of SET. The syntax is as follows -

set @anyVariableName:= 0;

The syntax for creating cumulative sum column in MySQL is as follows -

select yourColumnName1,yourColumnName2,........N,(@anyVariableName := @anyVariableName + yourColumnName2) as anyVariableName
from yourTableName order by yourColumnName1;

To understand the above concept, let us create a table. Following is the query to create the table -

mysql> create table CumulativeSumDemo
   −> (
   −> BookId int,
   −> BookPrice int
   −> );
Query OK, 0 rows affected (0.67 sec)

Insert some records into the table with the help of select statement. The query to insert records is as follows -

mysql> insert into CumulativeSumDemo values(101,400);
Query OK, 1 row affected (0.15 sec)

mysql> insert into CumulativeSumDemo values(102,500);
Query OK, 1 row affected (0.16 sec)

mysql> insert into CumulativeSumDemo values(103,600);
Query OK, 1 row affected (0.16 sec)

mysql> insert into CumulativeSumDemo values(104,1000);
Query OK, 1 row affected (0.18 sec)

displays all the records that I have inserted with the help of insert command. The query is as follows -

mysql> select *from CumulativeSumDemo;

The following is the output -

+--------+-----------+
| BookId | BookPrice |
+--------+-----------+
|    101 |       400 |
|    102 |       500 |
|    103 |       600 |
|    104 |      1000 |
+--------+-----------+
4 rows in set (0.00 sec)

To add the cumulative sum column, you first need to create a variable. The query is as follows -

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

Implement the above syntax discussed at the beginning to add the cumulative sum column. The query is as follows -

mysql> select BookId,BookPrice,(@CumulativeSum := @CumulativeSum + BookPrice) as CumSum
   −> from CumulativeSumDemo order by BookId;

The following is the output. The cumulative sum column is also visible here -

+--------+-----------+--------+
| BookId | BookPrice | CumSum |
+--------+-----------+--------+
|    101 |       400 |    400 |
|    102 |       500 |    900 |
|    103 |       600 |   1500 |
|    104 |      1000 |   2500 |
+--------+-----------+--------+
4 rows in set (0.00 sec)

The above is the detailed content of How to create cumulative sum column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!