CREATE DATABASE example;
USE example;

CREATE TABLE locations (
    locationID              INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,    

    locationName            VARCHAR(50) NOT NULL,

	totalPacketCount		INTEGER UNSIGNED NOT NULL DEFAULT 0,
    totalVolume             DOUBLE(14,3) NOT NULL DEFAULT 0,
    totalWeight             DOUBLE(14,3) NOT NULL DEFAULT 0,

	UNIQUE INDEX idx_locations_name(locationName(50))
) ENGINE = InnoDB;

CREATE TABLE packets (
	packetID			INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    packetNumber        VARCHAR(50) NOT NULL,
    locationID          INTEGER UNSIGNED NOT NULL,
    pieceCount			INTEGER UNSIGNED NOT NULL DEFAULT 0,
    volume              DOUBLE(14,3) NOT NULL DEFAULT 0,
    weight              DOUBLE(14,3) NOT NULL DEFAULT 0,
    
	UNIQUE INDEX key_packets_packetNumber(packetNumber(50)),
	INDEX idx_packets_locationID(locationID)
) ENGINE = InnoDB;

INSERT INTO locations VALUES (1, 'Location1', 0, 0, 0);
-- 486 rows of sample data
INSERT INTO `packets` VALUES (1,'PPG0001174',1,132,2.534,0.000),(2,'PPG0000674',1,132,2.534,0.000),(3,'PPG0000628',1,132,2.534,0.000),(4,'PPG0000626',1,132,2.534,0.000),(5,'PPG0000613',1,132,2.534,0.000),(6,'PPG0099107',1,132,2.534,0.000),(7,'PPG0099085',1,132,2.534,0.000),(8,'PPG0000529',1,132,2.534,0.000),(9,'PPG0000566',1,132,2.534,0.000),(10,'PPG0000554',1,132,2.534,0.000),(11,'PPG0000552',1,132,2.534,0.000),(12,'PPG0001000',1,132,2.534,0.000),(13,'PPG0000989',1,132,2.534,0.000),(14,'PPG0001018',1,132,2.534,0.000),(15,'PPG0001005',1,132,2.534,0.000),(16,'PPG0001552',1,132,2.534,0.000),(17,'PPG0001542',1,132,2.534,0.000),(18,'PPG0001535',1,132,2.534,0.000),(19,'PPG0001568',1,132,2.534,0.000),(20,'PPG0001567',1,132,2.534,0.000),(21,'PP20309228',1,132,2.534,0.000),(22,'PPG2031547',1,132,2.534,0.000),(23,'PPG0000987',1,132,2.534,0.000),(24,'PPG0001047',1,132,2.534,0.000),(25,'PPG0001036',1,132,2.534,0.000),(26,'PPG0001041',1,132,2.534,0.000),(27,'PPG0001058',1,132,2.534,0.000),(28,'PPG0000112',1,132,2.534,0.000),(29,'PPG0000400',1,132,2.534,0.000),(30,'PPG0000661',1,132,2.534,0.000),(31,'PPG0000687',1,132,2.534,0.000),(32,'PPG0000653',1,132,2.534,0.000),(33,'PPG0000643',1,132,2.534,0.000),(34,'PPG0000541',1,132,2.534,0.000),(35,'PPG0000818',1,132,2.534,0.000),(36,'PPG0000754',1,132,2.534,0.000),(37,'PPG0000705',1,132,2.534,0.000),(38,'PPG0000741',1,132,2.534,0.000),(39,'PPG0000530',1,132,2.534,0.000),(40,'PPG0000853',1,132,2.534,0.000);

DELIMITER //
DROP PROCEDURE IF EXISTS spExample1;
CREATE PROCEDURE spExample1() SQL SECURITY INVOKER
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE lid INTEGER;
	DECLARE tPieces INTEGER;
	DECLARE tVolume1 DOUBLE;
	DECLARE tVolume2 DOUBLE(14,3);
	DECLARE tVolume3 DECIMAL(14,3);
	DECLARE tWeight DOUBLE(14,3);
	DECLARE cur1 CURSOR FOR 
		SELECT locationID AS x, COUNT(*), SUM(volume), SUM(volume), SUM(volume), SUM(weight)
		FROM packets
		GROUP BY locationID;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
		
	OPEN cur1;
	
	REPEAT
		FETCH cur1 INTO lid, tPieces, tVolume1, tVolume2, tVolume3, tWeight;
		IF NOT done THEN
			SELECT lid, tPieces, tVolume1, tVolume2, tVolume3, tWeight;
			SET @s = CONCAT('UPDATE locations SET totalPacketCount = ' , tPieces , ', totalVolume = ', tVolume1, ', totalWeight = ', tWeight, ' WHERE locationID = ', lid);	
			SELECT @s;
			SET @s = CONCAT('UPDATE locations SET totalPacketCount = ' , tPieces , ', totalVolume = ', tVolume2, ', totalWeight = ', tWeight, ' WHERE locationID = ', lid);	
			SELECT @s;
			SET @s = CONCAT('UPDATE locations SET totalPacketCount = ' , tPieces , ', totalVolume = ', tVolume3, ', totalWeight = ', tWeight, ' WHERE locationID = ', lid);	
			SELECT @s;
			-- PREPARE stmt FROM @s;
			-- EXECUTE stmt;
		END IF;	
	UNTIL done END REPEAT;
END;
//
DELIMITER ;

CALL spExample1();