How to create cumulative sum column in MySQL?-Mysql Tutorial-php.cn

How to create cumulative sum column in MySQL?

王林

Release： 2023-09-12 12:09:04

forward

1103 people have browsed it

如何在 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;

Copy after login

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;

Copy after login

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

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

Copy after login

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)

Copy after login

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

mysql> select *from CumulativeSumDemo;

Copy after login

The following is the output -

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

Copy after login

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)

Copy after login

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
   &minus;> from CumulativeSumDemo order by BookId;

Copy after login

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)

Copy after login

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!