ShanghaiD
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:
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
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');
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');
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)
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
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
How can I do this?ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER