Professional Web Applications Themes

Wrong sum with float and group by - MySQL

Hallo, I have a problem with a query like that: SEMECT SUM(price) as total FROM product GROUP BY type price is float or double. total is not the exact sum. How can I do? Thank you in advance, Mario....

  1. #1

    Default Wrong sum with float and group by

    Hallo,
    I have a problem with a query like that:
    SEMECT SUM(price) as total FROM product GROUP BY type

    price is float or double.
    total is not the exact sum.
    How can I do?
    Thank you in advance,
    Mario.
    _mario.lat Guest

  2. #2

    Default Re: Wrong sum with float and group by

    _mario.lat wrote: 

    Mario,

    What do you mean "total is not the exact sum"?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Wrong sum with float and group by

    >I have a problem with a query like that: 

    There is no exact representation in binary floating point
    for most decimal numbers that are not exact integers.

    Fixes (none particularly satisfactory):

    - Use a decimal type that MySQL doesn't use floating point calculations on.
    (Not sure if there are any, but the DECIMAL types are good to try)
    - Represent currency as an integer number of cents or other smallest unit
    in a float, double, or 64-bit integer.
    - Explicitly round the sum (which might make things worse)
    - Live with it.


    Gordon Guest

  4. #4

    Default Re: Wrong sum with float and group by

    On Tue, 07 Nov 2006 06:40:48 -0500, Jerry Stuckle wrote:
     
    >
    > Mario,
    >
    > What do you mean "total is not the exact sum"?[/ref]

    Thankyou to all for answering (and reading) me.

    If you have a table with the value:
    type,price
    a,1.2
    b,0.1
    c,3.4

    real SUM (or real total) is 1.2+0.1+3.4 = 4.6
    the query
    SELECT SUM(price) as total FROM product GROUP BY type
    give me: total 4.5 for example.

    Thankyou again,
    Mario.
    _mario.lat Guest

  5. #5

    Default Re: Wrong sum with float and group by

    _mario.lat wrote:
     
    >>
    >> Mario,
    >>
    >> What do you mean "total is not the exact sum"?[/ref]
    >
    > Thankyou to all for answering (and reading) me.
    >
    > If you have a table with the value:
    > type,price
    > a,1.2
    > b,0.1
    > c,3.4
    >
    > real SUM (or real total) is 1.2+0.1+3.4 = 4.6[/ref]


    4.7 actually.

     


    I can't te that. I suspect you aren't telling us the full story.



    mysql> SELECT * FROM math;
    +----+-------+------+
    | id | price | type |
    +----+-------+------+
    | 1 | 1.2 | 1 |
    | 2 | 0.1 | 1 |
    | 3 | 3.4 | 1 |
    +----+-------+------+
    3 rows in set (0.00 sec)

    mysql> SELECT SUM(price) AS total FROM math GROUP BY type;
    +------------------+
    | total |
    +------------------+
    | 4.70000014454126 |
    +------------------+
    1 row in set (0.00 sec)

    mysql> SELECT FORMAT(SUM(price),1) AS total FROM math GROUP BY type;
    +-------+
    | total |
    +-------+
    | 4.7 |
    +-------+
    1 row in set (0.00 sec)




    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  6. #6

    Default Re: Wrong sum with float and group by

    _mario.lat wrote: 
    >>
    >>Mario,
    >>
    >>What do you mean "total is not the exact sum"?[/ref]
    >
    >
    > Thankyou to all for answering (and reading) me.
    >
    > If you have a table with the value:
    > type,price
    > a,1.2
    > b,0.1
    > c,3.4
    >
    > real SUM (or real total) is 1.2+0.1+3.4 = 4.6
    > the query
    > SELECT SUM(price) as total FROM product GROUP BY type
    > give me: total 4.5 for example.
    >
    > Thankyou again,
    > Mario.[/ref]

    Mario,

    Hmmm, I agree with Brian. I can't te your problem either. You
    will have a slight error due to floating point storage format as
    indicated by Gordon, but it should be quite a bit smaller than what you
    show.

    What do you get if you do a SELECT type, total FROM product?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  2. wrong group
    By Dave in forum PERL Beginners
    Replies: 0
    Last Post: December 25th, 07:06 AM
  3. #25562 [Opn->Bgs]: Float to String to Float conversion error
    By helly@php.net in forum PHP Development
    Replies: 0
    Last Post: September 16th, 08:02 PM
  4. #25562 [NEW]: Float to String to Float conversion error
    By daseymour at 3hc dot org in forum PHP Development
    Replies: 0
    Last Post: September 16th, 07:36 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139