calculate cumulative comumn in mysql

Question

I am Trying to Create cumulative column but unable to create it, please have look what i tried to calculate it.

mytable

create table mytable (
    TotalQuantity Decimal(7)
); 
insert into mytable (TotalQuantity) values
(0.0),
(0.0),
(1.0),
(0.0),
(2.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(1.0),
(1.0),
(0.0),
(1.0);

Based on above data set i wanted to calculate distinct wise count and cumulative sum

SELECT TotalQuantity AS DistinctTotalQuantity,
       COUNT(TotalQuantity) AS COUNTVALUE, 
       @running_total := @running_total + COUNT(TotalQuantity) AS cumulative_sum
FROM mytable 
JOIN (SELECT @running_total := 0) r
GROUP BY TotalQuantity

try https://stackoverflow.com/questions/12668785/cumulating-value-of-current-row-sum-of-previous-rows — davidchoo12, Oct 14 '17 at 08:57
You're merging two methods. Either do the group by in a subquery, and the variable addition in the outer query, or switch to using one or other of the methods only. — Strawberry, Oct 14 '17 at 08:59
@davidchoo12 I doubt that the OP's mysql version is that up to date — Strawberry, Oct 14 '17 at 09:00

Tim Biegeleisen · Answer 1 · 2017-10-14T09:29:45.993

3

The perhaps canonical ANSI standard way of doing a cumulative sum is via a correlated subquery. I make use of a view here which contains your base query:

CREATE VIEW test AS
SELECT
    TotalQuantity AS DistinctTotalQuantity,
    COUNT(TotalQuantity) AS COUNTVALUE, 
FROM mytable 
WHERE StoreId = 210 AND ProdName = 'Tusq'
GROUP BY TotalQuantity

Then compute the running total via:

SELECT
    t1.DistinctTotalQuantity,
    t1.COUNTVALUE,
    (SELECT SUM(t2.COUNTVALUE) FROM test t2
     WHERE t2.DistinctTotalQuantity <= t1.DistinctTotalQuantity) AS cumulative_sum
FROM test t1;

Using a session variable we could try:

SET @total = 0;
SELECT
    DistinctTotalQuantity,
    COUNTVALUE,
    (@total := @total + COUNTVALUE) AS cumulative_sum
FROM test;

edited Oct 14 '17 at 09:29

answered Oct 14 '17 at 09:11

Tim Biegeleisen

502,043
27
286
360

it looks too expensive query, can't we do same using variable? – Juned Ansari Oct 14 '17 at 09:24
@JunedAnsari You could use a session variable to compute the rolling sum if you want. But part of the performance problem here is that you need to aggregate in one query and then take a rolling sum. This isn't the most performant situation. – Tim Biegeleisen Oct 14 '17 at 09:31

Harshil Doshi · Accepted Answer · 2017-10-17T08:18:14.753

1

Following query will work perfectly and doesn't require too much changes in your query/approach. Although, I am not so sure about the performance:

SELECT TotalQuantity AS DistinctTotalQuantity,
       COUNT(TotalQuantity) AS COUNTVALUE, 
       (select @running_total := @running_total + count(TotalQuantity) from mytable m 
     where m.TotalQuantity = m1.TotalQuantity) cumulative_sum
    FROM (select *from mytable order by TotalQuantity) m1
    JOIN (SELECT @running_total := 0) r
    GROUP BY m1.TotalQuantity;

If you want to use floating values then declare column as follows:

create table `mytable` (
    `TotalQuantity` Decimal (7,1)  //Here 1 is scale. It means 1 digit is allowed after decimal point.
)

Here is my updated Sqlfiddle

Hope it helps!

edited Oct 17 '17 at 08:18

answered Oct 14 '17 at 09:44

Harshil Doshi

3,497
3
14
37

Hey Harshil, it is getting wrong if value changed. http://sqlfiddle.com/#!9/95166f/1 – Juned Ansari Oct 17 '17 at 07:29
Do you want to count 1.5 as seperate ToatalQuantity? – Harshil Doshi Oct 17 '17 at 07:51
The answer is accepted so I assume it's working and hence updated my answer. – Harshil Doshi Oct 17 '17 at 08:19

score 0 · Answer 3 · answered Oct 14 '17 at 08:58

0

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;


set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from mytable
order by id;

answered Oct 14 '17 at 08:58

Ravi Chauhan

1,409
13
26

calculate cumulative comumn in mysql

3 Answers3