Hi there, your help would be very appreciated.
I need translate this count in mysql:
my_Flight_zone my_number_of_flight_hours_w my_number_of_flight_hours_e Month Year
TO5N 18330 10123 1 2011
TO5N 18669 9750 2 2011
TO5N 20532 10911 3 2011
(18330 + 10123) = 28453 `tot h previous Month 1`
(18669 + 9750) = 28419 `tot h previous Month 2`
(20532 + 10911) = 31443 `tot h current Month`
(31443*14)/31 = 14200 `Partial current month`
(28453 + 28419 + 14200) = 71072 `q`
This is the try statement:
SELECT
x.my_Flight_zone
, (x.my_number_of_flight_hours_w + x.my_number_of_flight_hours_e) `tot h previous Month`
, (y.my_number_of_flight_hours_w + y.my_number_of_flight_hours_e) `tot h current Month`
, (x.my_number_of_flight_hours_w + x.my_number_of_flight_hours_e) +
(y.my_number_of_flight_hours_w + y.my_number_of_flight_hours_e) `tot h`
, DAYOFMONTH(CURDATE()) `day of current month`
, DAYOFMONTH(LAST_DAY(CURDATE())) `number days of current month`
, ROUND(((y.my_number_of_flight_hours_w + y.my_number_of_flight_hours_e)+
(x.my_number_of_flight_hours_w + x.my_number_of_flight_hours_e)*
DAYOFMONTH(CURDATE())/DAYOFMONTH(LAST_DAY(CURDATE()))),2) `q`
FROM dotableone x
LEFT JOIN dotableone y
ON (y.`Month` = x.`Month` + 1
AND y.`Year` = x.`Year`
AND y.my_Flight_zone = x.my_Flight_zone)
WHERE 1
AND x.my_Flight_zone = 'TO5N'
GROUP BY x.my_Flight_zone, x.`Year`, x.`Month`;
And this the output:
my_Flight_zone tot h previous Month tot h current Month tot h day of current month number days of current month q
TO5N 28453 28419 56872 14 31 41268,74
TO5N 28419 31443 59862 14 31 44277,39
TO5N 31443 14 31
Why the q
alias in the manual count is 71072 and in the statement is 41268.74 ?
Can you help me?
thanks a lot.
dotableone
:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dotableone`
-- ----------------------------
DROP TABLE IF EXISTS `dotableone`;
CREATE TABLE `dotableone` (
`my_Flight_zone` varchar(255) default NULL,
`my_number_of_flight_hours_w` int(10) default NULL,
`my_number_of_flight_hours_e` int(10) default NULL,
`Month` int(2) default NULL,
`Year` int(4) default NULL,
`ID` int(10) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of dotableone
-- ----------------------------
INSERT INTO dotableone VALUES ('TO1M', '9035', '5822', '1', '2011', '1');
INSERT INTO dotableone VALUES ('TO1N', '8499', '5384', '1', '2011', '2');
INSERT INTO dotableone VALUES ('TO1O', '8915', '6918', '1', '2011', '3');
INSERT INTO dotableone VALUES ('TO1P', '5135', '4838', '1', '2011', '4');
INSERT INTO dotableone VALUES ('TO1Q', '10284', '7045', '1', '2011', '5');
INSERT INTO dotableone VALUES ('TO5M', '8906', '6358', '1', '2011', '6');
INSERT INTO dotableone VALUES ('TO5N', '18330', '10123', '1', '2011', '7');
INSERT INTO dotableone VALUES ('TO5P', '6851', '5136', '1', '2011', '8');
INSERT INTO dotableone VALUES ('TO5Q', '3487', '3769', '1', '2011', '9');
INSERT INTO dotableone VALUES ('TO5R', '8415', '5146', '1', '2011', '10');
INSERT INTO dotableone VALUES ('TO6M', '9087', '5938', '1', '2011', '11');
INSERT INTO dotableone VALUES ('TO6N', '3986', '3840', '1', '2011', '12');
INSERT INTO dotableone VALUES ('TO6O', '6434', '5492', '1', '2011', '13');
INSERT INTO dotableone VALUES ('TOSR', '0', '20732', '1', '2011', '14');
INSERT INTO dotableone VALUES ('TOER', '267', '22208', '1', '2011', '15');
INSERT INTO dotableone VALUES ('TOVM', '0', '5601', '1', '2011', '16');
INSERT INTO dotableone VALUES ('TOSA', '0', '790', '1', '2011', '17');
INSERT INTO dotableone VALUES ('TO1M', '8911', '6221', '2', '2011', '18');
INSERT INTO dotableone VALUES ('TO1N', '8477', '5865', '2', '2011', '19');
INSERT INTO dotableone VALUES ('TO1O', '8904', '7020', '2', '2011', '20');
INSERT INTO dotableone VALUES ('TO1P', '5431', '4965', '2', '2011', '21');
INSERT INTO dotableone VALUES ('TO1Q', '9749', '7475', '2', '2011', '22');
INSERT INTO dotableone VALUES ('TO5M', '8563', '6754', '2', '2011', '23');
INSERT INTO dotableone VALUES ('TO5N', '18669', '9750', '2', '2011', '24');
INSERT INTO dotableone VALUES ('TO5P', '6613', '5152', '2', '2011', '25');
INSERT INTO dotableone VALUES ('TO5Q', '3555', '4036', '2', '2011', '26');
INSERT INTO dotableone VALUES ('TO5R', '8714', '5847', '2', '2011', '27');
INSERT INTO dotableone VALUES ('TO6M', '9401', '6175', '2', '2011', '28');
INSERT INTO dotableone VALUES ('TO6N', '3924', '3649', '2', '2011', '29');
INSERT INTO dotableone VALUES ('TO6O', '6971', '5844', '2', '2011', '30');
INSERT INTO dotableone VALUES ('TOSR', '0', '21370', '2', '2011', '31');
INSERT INTO dotableone VALUES ('TOER', '329', '22396', '2', '2011', '32');
INSERT INTO dotableone VALUES ('TOVM', '0', '5574', '2', '2011', '33');
INSERT INTO dotableone VALUES ('TOSA', '0', '710', '2', '2011', '34');
INSERT INTO dotableone VALUES ('TO5N', '20532', '10911', '3', '2011', '35');
INSERT INTO dotableone VALUES ('TO1M', '9912', '6554', '3', '2011', '36');
INSERT INTO dotableone VALUES ('TO1N', '9597', '6172', '3', '2011', '37');
INSERT INTO dotableone VALUES ('TO1O', '9643', '7810', '3', '2011', '38');
INSERT INTO dotableone VALUES ('TO1P', '6132', '4935', '3', '2011', '39');
INSERT INTO dotableone VALUES ('TO1Q', '11008', '8200', '3', '2011', '40');
INSERT INTO dotableone VALUES ('TO5M', '9791', '6895', '3', '2011', '41');
INSERT INTO dotableone VALUES ('TO5P', '7669', '5452', '3', '2011', '42');
INSERT INTO dotableone VALUES ('TO5Q', '4265', '4227', '3', '2011', '43');
INSERT INTO dotableone VALUES ('TO5R', '9214', '6109', '3', '2011', '44');
INSERT INTO dotableone VALUES ('TO6M', '10608', '6663', '3', '2011', '45');
INSERT INTO dotableone VALUES ('TO6N', '4438', '4499', '3', '2011', '46');
INSERT INTO dotableone VALUES ('TO6O', '7017', '6015', '3', '2011', '47');
INSERT INTO dotableone VALUES ('TOER', '327', '24889', '3', '2011', '48');
INSERT INTO dotableone VALUES ('TOSA', '0', '809', '3', '2011', '49');
INSERT INTO dotableone VALUES ('TOSR', '0', '23223', '3', '2011', '50');
INSERT INTO dotableone VALUES ('TOVM', '0', '6463', '3', '2011', '51');