How to get cumulative daily sales from a table

I have 2 simple tables, one is a calendar listing of all days in a month and the other is a table showing sales amounts on specific days:

Calendar:
-- ----------------------------
-- Table structure for `calendar`
-- ----------------------------
DROP TABLE IF EXISTS `calendar`;
CREATE TABLE `calendar` (
  `Date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of calendar
-- ----------------------------
INSERT INTO `calendar` VALUES ('2011-03-01');
INSERT INTO `calendar` VALUES ('2011-03-02');
INSERT INTO `calendar` VALUES ('2011-03-03');
INSERT INTO `calendar` VALUES ('2011-03-04');
INSERT INTO `calendar` VALUES ('2011-03-05');
INSERT INTO `calendar` VALUES ('2011-03-06');
INSERT INTO `calendar` VALUES ('2011-03-07');
INSERT INTO `calendar` VALUES ('2011-03-08');
INSERT INTO `calendar` VALUES ('2011-03-09');
INSERT INTO `calendar` VALUES ('2011-03-10');
INSERT INTO `calendar` VALUES ('2011-03-11');
INSERT INTO `calendar` VALUES ('2011-03-12');
INSERT INTO `calendar` VALUES ('2011-03-13');
INSERT INTO `calendar` VALUES ('2011-03-14');
INSERT INTO `calendar` VALUES ('2011-03-15');
INSERT INTO `calendar` VALUES ('2011-03-16');
INSERT INTO `calendar` VALUES ('2011-03-17');
INSERT INTO `calendar` VALUES ('2011-03-18');
INSERT INTO `calendar` VALUES ('2011-03-19');
INSERT INTO `calendar` VALUES ('2011-03-20');
INSERT INTO `calendar` VALUES ('2011-03-21');
INSERT INTO `calendar` VALUES ('2011-03-22');
INSERT INTO `calendar` VALUES ('2011-03-23');
INSERT INTO `calendar` VALUES ('2011-03-24');
INSERT INTO `calendar` VALUES ('2011-03-25');
INSERT INTO `calendar` VALUES ('2011-03-26');
INSERT INTO `calendar` VALUES ('2011-03-27');
INSERT INTO `calendar` VALUES ('2011-03-28');
INSERT INTO `calendar` VALUES ('2011-03-29');
INSERT INTO `calendar` VALUES ('2011-03-30');
INSERT INTO `calendar` VALUES ('2011-03-31');

Open in new window

and:
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `Date` date NOT NULL,
  `A` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES ('2011-03-03', '20');
INSERT INTO `sales` VALUES ('2011-03-07', '15');
INSERT INTO `sales` VALUES ('2011-03-14', '25');
INSERT INTO `sales` VALUES ('2011-03-15', '10');
INSERT INTO `sales` VALUES ('2011-03-30', '5');
INSERT INTO `sales` VALUES ('2011-03-31', '10');

Open in new window


I'm trying to end up with a table which shows for each day of the month the cumulative sales amounts. I've LEFT JOINed the tables with
SELECT calendar.Date, sales.A
FROM calendar LEFT JOIN sales using(Date)

Open in new window

which gives:
2011-03-01  null
2011-03-02  null
2011-03-03  20
2011-03-04  null
2011-03-05  null
2011-03-06  null
2011-03-07  15
2010-03-08  null
etc
etc

Open in new window

but what I want is for this to become:
2011-03-01  0
2011-03-02  0
2011-03-03  20
2011-03-04  20
2011-03-05  20
2011-03-06  20
2011-03-07  35
2010-03-08  35
etc
etc

Open in new window

How can I do this?

ShanghaiDAsked:
Who is Participating?
 
theGhost_k8Database ConsultantCommented:
set @a:=0;SELECT calendar.Date, ifnull(sales.A,0),(select @a:=@a+ifnull(sales.A,0)) CumulativeSum FROM calendar LEFT JOIN sales using(Date);
0
 
ShanghaiDAuthor Commented:
Thank you!  I've learned something new!  Sorry for the delay in signing this off, the time zone difference with Shanghai China is to blame!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.