Get cumulative sum with using group by

Question

I have to calculate Cumulative Sum from table. I checked below questions from forum. Create a Cumulative Sum Column in MySQL

Php Script to Calculate Cumulative Totals for Accounts

But I could not create corrected query for this.

I have tried below query.

SET @CumulativeSum := 0;

SELECT ItemId,
       sum(Quantity) Quantity,
       @CumulativeSum = @CumulativeSum + sum(Quantity) AS CumulativeSum,
       weekofyear(UploadedIn) WeekNo
  FROM testtbl
GROUP BY ItemId, weekofyear(UploadedIn)
ORDER BY ItemId, weekofyear(UploadedIn);

Below is the sample data

CREATE TABLE `testtbl` (
  `ItemId` int(11) DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  `UploadedIn` datetime DEFAULT NULL
) ;


INSERT INTO  testtbl (1, 50, '01-01-2009 00:00:00'), 
(1, 25, '01-01-2009 00:00:00'), 
(1, 25, '01-01-2009 00:00:00'), 
(1, -100, '04-12-2009 16:34:33'), 
(1, 10, '19-09-2018 16:34:33'), 
(1, 10, '19-09-2018 16:40:47'), 
(1, -19, '19-09-2018 16:41:48'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, 1, '20-03-2013 16:49:47'), 
(3, -3, '21-03-2013 09:05:00'), 
(3, 3, '21-03-2013 09:05:01'), 
(3, -3, '05-04-2013 10:27:00'), 
(3, 1, '25-04-2013 16:07:18'), 
(3, -1, '25-04-2013 16:07:34'), 
(3, 5, '29-04-2013 15:33:19'), 
(3, 25, '29-04-2013 15:40:18'), 
(3, 100, '29-04-2013 15:40:18'), 
(3, 10, '29-04-2013 15:40:18'), 
(3, 10, '29-04-2013 15:40:18'), 
(3, 20, '29-04-2013 15:40:18'), 
(3, 100, '29-04-2013 15:40:18'), 
(3, 10, '07-05-2013 07:21:49'), 
(3, 10, '07-05-2013 07:21:49'), 
(3, 5, '07-05-2013 07:21:49'), 
(3, 2, '07-05-2013 07:25:49'), 
(3, 6, '07-05-2013 07:32:05'), 
(3, 22, '07-05-2013 07:32:05'), 
(3, 10, '07-05-2013 07:32:05'), 
(3, 5, '07-05-2013 07:32:05'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 5, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 12, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 100, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 50, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 5, '07-05-2013 08:19:23'), 
(3, 2, '07-05-2013 08:19:23'), 
(3, 12, '07-05-2013 08:19:23'), 
(3, -682, '07-05-2013 09:33:02'), 
(3, 4, '08-05-2013 07:08:14'), 
(3, -4, '08-05-2013 09:41:29'), 
(3, 1, '08-05-2013 15:38:13'), 
(3, -1, '08-05-2013 15:54:35'), 
(3, 1, '30-05-2013 08:20:02'), 
(3, 2, '14-06-2013 15:23:02')

Expected Result

ItemId  Quantity    CumulativeSum   WeekNo
1       100             100             1
1       1               101             38
1       -100            1               49
3       3               3               12
3       -3              0               14
3       0               0               17
3       270             270             18
3       -270            0               19
3       1               1               22
3       2               3               24

Your test data/script is broken and doesn't work. – Tim Biegeleisen May 17 '19 at 03:38 — Tim Biegeleisen, May 17 '19 at 03:38

Tim Biegeleisen · Accepted Answer · 2019-05-17T03:42:27.170

In earlier versions of MySQL, you could handle this using a correlated subquery:

SELECT
    ItemId,
    WEEKOFYEAR(UploadedIn) WeekNo,
    SUM(Quantity) Quantity,
    (SELECT SUM(t2.Quantity) FROM testtbl t2
     WHERE WEEKOFYEAR(t2.UploadedIn) <= WEEKOFYEAR(t1.UploadedIn) AND
     t1.ItemId = t2.ItemId) AS CumulativeSum
FROM testtbl t1
GROUP BY
    ItemId,
    WEEKOFYEAR(UploadedIn)
ORDER BY
    ItemId,
    WEEKOFYEAR(UploadedIn);

Demo

In MySQL 8+, we can use SUM as an analytic function:

SELECT
    ItemId,
    WEEKOFYEAR(UploadedIn) WeekNo,
    SUM(Quantity) Quantity,
    SUM(SUM(Quantity)) OVER (PARTITION BY ItemId
                             ORDER BY WEEKOFYEAR(UploadedIn)) AS CumulativeSum
FROM testtbl
GROUP BY
    ItemId,
    WEEKOFYEAR(UploadedIn)
ORDER BY
    ItemId,
    WEEKOFYEAR(UploadedIn);

I have tried first query but it is not giving excepted output. — Nandu, May 17 '19 at 03:37
I fixed both queries. Note that your sample insert statement is bogus, and won't even run on MySQL. — Tim Biegeleisen, May 17 '19 at 03:43

Get cumulative sum with using group by

1 Answers1

Demo