Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i have a problem creating cumulative function in mysql database.

let say i have a stored procedure which getting data and counting quantity/sum_quantity

example A table consist of consumer goods which has attributes {name, brand, quantity}

Row 1 = {cookies, brand z, 100}.

Row 2 = {tissue, brand a, 200}.

Row 3 = {toothpaste, brand v, 100}.

which has sum_quantity = 400;

then i create stored procedure to divide quantity/sum_quantity and i get the result

Row 1 = {cookies, brand z, 100, 0.25} ; 0.25 is result from division method (quantity/sum_quantity)

Row 2 = {tissue, brand a, 200, 0.75} ; 0.75 is result from division method in First Row then Added to division method in second row. and so on.

[the real case]

i have done the stored procedure to divide quantity/sum_quantity using the code below:

 DELIMITER $$

 USE `sre`$$

 DROP PROCEDURE IF EXISTS `sp_checkPotentialProduct`$$

 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkPotentialProduct`()
   BEGIN 
DECLARE sumtotal INT DEFAULT 0;
DECLARE first_margin DOUBLE;
DECLARE count_rows INT DEFAULT 0;
DECLARE startnum INT DEFAULT 0;

/* assign values to variables */
SELECT SUM(sum_product) INTO sumtotal FROM `vShowTopSellingQuantity12`;
SELECT COUNT(sum_product) INTO count_rows FROM `vshowtopsellingquantity12`;
SELECT (sum_product/sumtotal)*100 INTO first_margin FROM `vshowtopsellingquantity12`  LIMIT 1; 
    /*creating algorithm to analyze the percentage quantity/total */

SELECT `idms_kodebarang`, `nama`, sum_product , (sum_product/sumtotal)*100 AS '%', "THE CUMULATIVE FUNCTION GOES HERE" AS 'startnum' FROM `vShowTopSellingQuantity12`;
  END$$

DELIMITER ;

TABLE SOURCE

table source

i want to cumulative the (sum_product/sumtotal) inserted into the column "THE CUMULATIVE FUNCTION GOES HERE" so, the first row will counting like my excel below

cumulative table

any ideas to do this?

thanks.

share|improve this question
    
is there anyone willing to help? –  randytan Dec 10 '13 at 10:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.