Link to home
Create AccountLog in
Avatar of ShanghaiD
ShanghaiDFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?

ASKER CERTIFIED SOLUTION
Avatar of theGhost_k8
theGhost_k8
Flag of India image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of ShanghaiD

ASKER

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!