subtract and total query

Tokkaido · May 21st, 2011, 09:18 PM

Hello, could anyone help me with the following please:

I have a table with the following values:

date, value, event, element
2011-2-2, 1000 , 'input', 1
2011-7-6, 450 , 'input', 3
2011-3-2, 2000 , 'output', 1
2011-5-4, 200 , 'input', 1
2011-8-8, 500 , 'output', 3
2011-6-7, 450 , 'output', 1
2011-1-3, 230 , 'input', 2
2011-9-4, 1000 , 'input', 3
2011-3-4, 800 , 'input', 4
2011-4-9, 530 , 'output', 2
2011-5-1, 850 , 'output', 4

some rules to understand the context:

Every event 'output' has its event 'input' in an earlier date
Not all 'input' have an 'output' yet
Always 'output' value is greater than the 'input' value related.

What I need here is to generate a query that returns me the results of subtraction coupled between inputs and outputs, for example should return:

total element
1250, 1
300,2
50,3
50,4

for example:

element 1 output value 2000 in 2011-3-2 minus input value 1000 in 2011-2-2 equal subtotal 1000 plus the same element 1 output value 450 in 2011-6-7 minus input value 200 in 2011-5-4 equal subtotal 250, total: 1250.

I spend days in this and my brain is in pain.

I add some data for the soul who want to help me

PHP Code:


			
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for events
-- ----------------------------
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `value` INT(11) NOT NULL,
  `event` VARCHAR(20) COLLATE utf8_spanish_ci NOT NULL,
  `element` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
 
-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `events` VALUES ('1', '2011-02-02', '1000', 'input', '1');
INSERT INTO `events` VALUES ('2', '2011-07-06', '450', 'input', '3');
INSERT INTO `events` VALUES ('3', '2011-03-02', '2000', 'output', '1');
INSERT INTO `events` VALUES ('4', '2011-05-04', '200', 'input', '1');
INSERT INTO `events` VALUES ('5', '2001-08-08', '500', 'output', '3');
INSERT INTO `events` VALUES ('6', '2011-06-07', '450', 'output', '1');
INSERT INTO `events` VALUES ('7', '2011-01-03', '230', 'input', '2');
INSERT INTO `events` VALUES ('8', '2011-09-04', '1000', 'input', '3');
INSERT INTO `events` VALUES ('9', '2011-03-04', '800', 'input', '4');
INSERT INTO `events` VALUES ('10', '2011-04-09', '530', 'output', '2');
INSERT INTO `events` VALUES ('11', '2011-05-01', '850', 'output', '4');

Thanks in advance

UseLess · May 26th, 2011, 03:07 AM

Greetings,

Use a stored procedure;

Code:

DELIMITER //
DROP PROCEDURE IF EXISTS GetValue //
CREATE PROCEDURE GetValue(OUT total INT)
BEGIN
	DECLARE total_one INT default 0;
	DECLARE total_two INT default 0;
	SELECT sum(value) INTO total_one FROM events WHERE event='input' AND element=1;
	SELECT sum(value) INTO total_two FROM events WHERE event='output' AND element=1;
	
	SELECT SUM(total_two - total_one) INTO total;
	end //
DELIMITER ;

and call it with the following:

Code:

call GetValue(@total);
select @total

Tokkaido · May 26th, 2011, 10:59 PM

Thanks for your answer and time, currently I solve this problem adding a column with timestamp value wich I create in the moment when I add an "input" row, and when I add a "output" row I copy that value therefore I "know" exactly wich pair of rows are related, but I will study the code You so kindly share with me. Cordially

HSO