SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict Ramiro S's Avatar
    Join Date
    May 2003
    0 Post(s)
    0 Thread(s)

    Query Help (Am I Blinded?)

    Hi. I have this query:

      select arrival,id,(sales+ taxes) as sale,paid
      from reservations
      where filestat > 10 
      and ((sales+taxes) - paid) >0 order by 1 LIMIT 0, 100
    Basically (sales+tax)-paid gives me the balance of the account. The problem is MySQL is giving me rows like this in the result (example):

    sales = 100,51 , tax=15,11 (115,62 total price)... paid 115,62. So that row should not be there, in the result. Column types are DECIMAL 10,2.

    Whats going on!?!?!?!? I'm using MySQL 4.0.20-standard

    Thank you,
    Quasar - Web Development - Free Avatars

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    i think this is due to rounding errors

    mysql performs decimal arithmetic using float values
    Quote Originally Posted by mysql
    DECIMAL and NUMERIC values are stored as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values... The maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale.
    some people deal with this problem by storing monetary values as integers (representing cents) | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

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