Problems with MySQL count using alias

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');

you need to use another set of parentheses in your ROUND expression

you have this –

ROUND(
       (    (y.foo + y.bar)
          + (x.foo + x.bar) 
          * DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE()))
       )
   ,2) `q`

can you see the problem more clearly now? you’ve got several parenthese that aren’t really needed, but you’re missing the parentheses around the amounts that you want to multiply by the date ratio

also, another problem…

ON (y.`Month` = x.`Month` + 1 
AND  y.`Year` = x.`Year` ...

that’s ~not~ gonna work in december

Hello r937.

Thanks for reply, I have fix the problem with:

     , 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) +
        (y.my_number_of_flight_hours_w + y.my_number_of_flight_hours_e) *
        DAYOFMONTH(CURDATE())/DAYOFMONTH(LAST_DAY(CURDATE()))
       ,2) `q`

The output now is correct: 74062.06 :slight_smile:

In the table dotableone I have for single my_Flight_zone and for month and for year this:

  1. my_number_of_flight_hours_w
  2. my_number_of_flight_hours_e

SELECT LEFT(my_Flight_zone,2)
    , `MONTH`
    , SUM(my_number_of_flight_hours_w+my_number_of_flight_hours_e)
FROM dotableone
GROUP BY 
    LEFT(my_Flight_zone,2), `MONTH`;

my_Flight_zone	MONTH	totalSum
TO		1	232771
TO		2	237014
TO		3	259979

I need calculate this output aggregate for LEFT(my_Flight_zone,2):
x = (232771+237014+(259979*14/31))*953.8)*1000)