This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am populating a cumulative SUM column for a table of MySQL.
For example, If we are working with sales and inventory management domain, every day we need to calculate cumulative sum of different columns like tock value, profit figure, expense figure.
Generally, we can use this kind of demonstration for report purpose only and if it is required to store, we can populate and store for permanent use.
Below is a small demonstration:
Create sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tbl_SampleSum ( ID INT ,SumValue INT ); INSERT INTO tbl_SampleSum VALUES (1,100),(2,150),(3,175) ,(4,250),(5,100),(6,88) ,(7,120),(8,130),(9,275); |
Populate cumulative SUM column using MySQL Variable:
|
1 2 3 4 5 6 7 |
SET @SumVariable := 0; SELECT ID ,SumValue ,(@SumVariable := @SumVariable + SumValue) AS CumulativeSum FROM tbl_SampleSum ORDER BY ID; |
Populate cumulative SUM column using MySQL Correlated query:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT B.ID ,B.SumValue ,( SELECT SUM(A.SumValue) FROM tbl_SampleSum AS A WHERE A.id <= B.id ) AS CumulativeSum FROM tbl_SampleSum AS B ORDER BY B.id |
The Result:
