Mysql query uses every row of another query

Question

I searched a lot, but cannot find a useful answer:

I want to have a list of totals from a user-defined period, giving me a start and end date. The totals should each time, starting from the start date, start from the start date and add every day 1 line. therefore, the last line gives the totals from start to finish. example: - set period = beginning of 2013-01-01, end = 2013-01-31

total day 1 = 100 total day 2 = 0 (not listed in my totalsperday query, but should have a row in my final query) total day 3 = 140 total day 4 = 20 ... final table should look like: end day 1: 100 end day 2: 100 end day 3: 240 end day 4: 260 ... 

so I have a query that calculates all days:

 SELECT '2013-01-01' as startdate, w.endDate FROM ( SELECT date('2013-01-01' + INTERVAL ui*100 + vi*10 + wi DAY) AS endDate FROM sysints AS u JOIN sysints AS v JOIN sysints AS w WHERE ( ui*100 + vi*10 + wi ) <= ( SELECT DATEDIFF( '2013-01-31','2013-01-01') as ddff ) ) w ORDER BY w.endDate ASC 

and I have a query that calculates the totals per day

 SELECT p.selldate, SUM(p.price) as totalPerDay FROM products p WHERE '2013-01-01' >= p.selldate <= '2013-01-31' GROUP BY p.selldate ORDER BY p.selldate ASC 

Now combining the two to get the final result is difficult.

Basically what the last request looks like:

 - make the sum of sumperday from day 1 to day 1 - make the sum of sumperday from day 1 to day 2 - make the sum of sumperday from day 1 to day 3 ... 

any help? thanks. This is a simplified example of my final request.

Answer 1

Below is a sample. The idea is to get an initial dataset, ordered by date and having cumulative totals, implicit date range entries. Then, with the help of the cursor, you can go through each row to get the final summary column (the amountCalc column in the sample), simply summing up the previous records - it will work because you already have columns sorted by date.

A procedure may have other I / O parameters. Instead of getting information from a table, you can get data from one view, where the view can already be ordered by asc date. This is just a sample that can be customized as needed.

Good luck.

 -- drop table `Balance`; CREATE TABLE `Balance` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `account` varchar(30) NOT NULL, `amount` DECIMAL(10,2) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `Balance` (`date`, `account`, `amount`) VALUES ('2013-01-02', 'T355176', 8700), ('2013-01-03', 'T355176', 8900), ('2013-01-04', 'T355215', 33308), ('2013-01-03', 'T355215', 116581), ('2013-01-06', 'T812022', 275000), ('2013-01-02', 'T812063', 136500), ('2013-01-05', 'T812063', 11682), ('2013-01-06', 'T812064', 615100), ('2013-01-03', 'T812064', 25000), ('2013-01-02', 'T812085', 82500); SELECT * FROM Balance WHERE date >= '2013-01-01' AND date <= '2013-01-06' ORDER BY date ASC; CALL sp_getTotals('2013-01-01', '2013-01-06'); -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_getTotals`(IN startDate DATE, IN endDate DATE) BEGIN DECLARE dt DATE; DECLARE amt DECIMAL(10,2); DECLARE amtCalcPart DECIMAL(10,2); DECLARE done INT DEFAULT 0; DECLARE dtStart DATE; DECLARE dtEnd DATE; DECLARE cur1 CURSOR FOR SELECT date, amount FROM `TempMB`; DECLARE cur2 CURSOR FOR SELECT startDate, endDate; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DROP TEMPORARY TABLE IF EXISTS `TempMB`; CREATE TEMPORARY TABLE IF NOT EXISTS `TempMB` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `amount` DECIMAL(10,2) NULL DEFAULT 0.00, `amountCalc` DECIMAL(10,2) NULL DEFAULT 0.00, PRIMARY KEY (`id`) ); SET dtStart = DATE(startDate); SET dtEnd = DATE(endDate); WHILE dtStart <= dtEnd DO INSERT INTO `TempMB` (`date`) SELECT dtStart; SET dtStart = DATE_ADD(dtStart, INTERVAL 1 DAY); END WHILE; SELECT * FROM TempMB; -- Fill temp table with info needed UPDATE `TempMB` t INNER JOIN ( SELECT date, SUM(amount) AS amount FROM Balance WHERE date >= startDate AND date <= endDate GROUP BY date ORDER BY date ASC ) b ON b.date = t.date SET t.amount = b.amount; /*INSERT INTO `TempMB` (`date`, `amount`) SELECT date, SUM(amount) AS amount FROM Balance WHERE date >= startDate AND date <= endDate GROUP BY date ORDER BY date ASC; */ SET amtCalcPart = 0.00; -- Initialise cursor OPEN cur1; -- USE BEGIN-END handler for cursor-control within own BEGIN-END block BEGIN DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; -- Loop cursor throu temp records LOOP -- Get next value FETCH cur1 INTO dt, amt; -- Calculate amountCalc SET amtCalcPart = (SELECT SUM(amount) as amt FROM `TempMB` WHERE Date <= dt); UPDATE `TempMB` SET amountCalc = amtCalcPart WHERE date = dt; END LOOP; END; -- Release cursor CLOSE cur1; SELECT * FROM TempMB; END