How to make accumulative count based on date in MySQL?

Question

I have the following table "testfinal"and would like to make it look like table "testfinal1" with accumulative sum based on orderdate.

testfinal

+------+------------+-------+--------+----------+
| ID   | Orderdate  | apple | banana | quantity |
+------+------------+-------+--------+----------+
| 1005 | 2015-05-05 |     2 |      0 |        2 |
| 1005 | 2015-05-05 |     0 |      2 |        2 |
| 1005 | 2015-05-06 |     0 |      1 |        1 |
| 1006 | 2011-05-06 |     0 |      3 |        3 |
| 1006 | 2011-10-06 |     1 |      0 |        1 |
+------+------------+-------+--------+----------+

testfinal1

+------+------------+-------+--------+----------+
| ID   | Orderdate  | apple | banana | quantity |
+------+------------+-------+--------+----------+
| 1005 | 2015-05-05 |     2 |      2 |        4 |
| 1005 | 2015-05-06 |     2 |      3 |        5 |
| 1006 | 2011-05-06 |     0 |      3 |        3 |
| 1006 | 2011-10-06 |     1 |      3 |        4 |
+------+------------+-------+--------+----------+

Now my code is as follows but it doesn't work

insert into testfinal1 (ID, Orderdate, apple, banana, quantity) 
select ID, Orderdate, 
(select sum(apple)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as apple, 
(select sum(banana)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as banana, 
(select sum(quantity)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as quantity 
from testfinal group by ID, Orderdate;

I think the problem is the term orderdate is not specified.

Why do you need the conditions? What's wrong with just doing `SELECT ID, Orderdate, SUM(apple), SUM(banana), SUM(quantity) FROM testfinal GROUP BY ID, Orderdate`? — Robby Cornelissen, May 06 '16 at 09:22
is the last row in `testfina1` table correct? `1006 | 2011-10-06 | 1 | 3 | 4 `. Isn't it supposed to be `1006 | 2011-10-06 | 1 | 0 | 4 ` this? — 1000111, May 06 '16 at 09:31
thanks for the comment. the reason why the last row is 1 | 3 | 4 it is because it is the result of (0 + 1), (3+0), (3+1) from the table testfinal. — Leigh Tsai, May 06 '16 at 09:58
on the original testfinal table it is the quantity goods ordered on the orderdate. I would like to make the new table "testfinal1" sum up the goods the ID bought base on the "orderdate" that is to say, the ID had bought (0,3) on 20110506, and had bought (1,0) seperately on 20111006. in the testfinal2 table i would like to show that the user ID 1006 had bought (1,3) in total from the past to the date 20111006. — Leigh Tsai, May 06 '16 at 09:59
@RobbyCornelissen thanks for the comment, because i would like to make it an cumulative sum based on dates & IDs. — Leigh Tsai, May 06 '16 at 10:03

1000111 · Answer 1 · 2016-05-06T09:53:28.113

Here's the query you might need to generate first:

SELECT
ID,
Orderdate,
SUM(apple) AS 'apple',
SUM(banana) AS 'banana',
SUM(quantity) AS 'quantity'
FROM testfinal
GROUP BY Orderdate;

DROP TABLE IF EXISTS testfinal1;

CREATE TABLE testfinal1 LIKE testfinal;

INSERT INTO testfinal1(ID,Orderdate,apple,banana,quantity)

SELECT
ID,
Orderdate,
SUM(apple) AS 'apple',
SUM(banana) AS 'banana',
SUM(quantity) AS 'quantity'
FROM testfinal
GROUP BY Orderdate;

How to make accumulative count based on date in MySQL?

1 Answers1