SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problems with MySQL count using alias

    Hi there, your help would be very appreciated.

    I need translate this count in mysql:
    Code:
    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:
    Code:
    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:
    Code:
    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`:
    Code:
    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');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Why the `q` alias in the manual count is 71072 and in the statement is 41268.74 ?
    Can you help me?
    you need to use another set of parentheses in your ROUND expression

    you have this --
    Code:
    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...
    Code:
    ON (y.`Month` = x.`Month` + 1 
    AND  y.`Year` = x.`Year` ...
    that's ~not~ gonna work in december
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello r937.

    Thanks for reply, I have fix the problem with:
    Code:
         , 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

    Quote Originally Posted by r937 View Post
    also, another problem...
    Code:
    ON (y.`Month` = x.`Month` + 1 
    AND  y.`Year` = x.`Year` ...
    that's ~not~ gonna work in december
    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

    Code:
    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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •