SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SUM() of one row != to actual value

    I've run across a problem that has exposed a hole in my understanding of MySQL, I'm hoping you experts can set me straight. I have a query that gets the sum of a column

    Code:
    Table `costs_payments`
         cost_id int(11)
         payment_id int(11)
         amount float
    
    SELECT SUM(amount) FROM costs_payments WHERE cost_id = [variable]
    When the query matches only one row, however, the sum is not as precise as the value in the amount column for that one row. For example:

    Code:
    SELECT * FROM costs_payments WHERE cost_id = 43
    
    returns one row:
    
    cost_id     payment_id     amount
       43           23           34.92
    
    
    SELECT SUM(amount) FROM costs_payments WHERE cost_id = 43
    
    SUM(amount)
     34.91988856
    Is this some internal MySQL storage thing I don't know about? Any ideas? Thanks!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    no, that's just the nature of float (i.e., single and double). if you're storing money, you should be using decimal instead.

  3. #3
    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 Bil
    Is this some internal MySQL storage thing I don't know about?
    that depends on if you know about FLOAT and that its values are approximations

    wouldn't DECIMAL(13,4) do just as well for monetary amounts?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    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)
    oops, sniped -- i was away from the computer and then posted without refreshing
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Location
    Beautiful Rhode Island, USA
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys. I was using Float instead of Decimal because I was under the impression that Ruby on Rails didn't do Decimal types (I'm going to look into this, though, because I may have made this up in my head)

    Thanks again!

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    float and decimal are both returned to the host langauge in a format the host language can understand; it's the responsibility of the host language's mysql driver to make sure this happens transparently. i have no doubt that ruby can handle a decimal data type, probably as a float.


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
  •