Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: Need a little help with MySQL round up to hundredth

    Hi all, my first post here.

    I'm looking for a way to round UP all values of a column to the hundredth place with an SQL statement.

    So any values like:
    12.2242 = 12.23 (rounded up at the hundredth)
    12.225 = 12.23 (true round to the hundredth)
    .11111 = .12 (rounded up at the hundredth)
    .115 = .12 (true round to the hundredth)

    My goal here is to round up every value in a column to the hundredth, in which the column will have several thousand different values.

    Is this possible?

    Select * from table_name update(column_name ???????);

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at using

    Code:
    UPDATE <table name> SET <column name> = <value>
    WHERE ...
    <value> can refer to its own column name, or refer to another column name or even have a function applied the column.

    The function that you will need to investigate more is ROUND(x, d) where x is the value and d is the number of decimal places.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ROUND will ~not~ do the job here by itself

    you have to add 0.005 to the value first

    then ROUND the result
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Good spot. We could also use the CEIL function i.e. CEIL(value*100)/100
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2010
    Posts
    4
    Quote Originally Posted by it-iss.com View Post
    Good spot. We could also use the CEIL function i.e. CEIL(value*100)/100
    Could you give me an example how this would work.

    If I understand the CEIL will move the value to an integer
    so .111 would be 1. In which case the formula would spit out (1*100)/100 = 1 but I need precision to the hundredth.

    Adding .004 seems like it would work

    .111 + .004 = .115 round = .12
    .117 + .004 = .121 round = .12
    .119 + .004 = .123 round = .12
    .120 + .004 = .124 round = .12

    However, I have never used a math function to update a database. Can you provide an example of one?

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    look again, the CEIL multiplies the numeric by 100 before it performs the CEIL operation and then divides it by 100. Have a look at the example below:

    Code:
    mysql> select * from test;
    +------+--------------+
    | name | salary       |
    +------+--------------+
    | emp1 | 1323243.3123 | 
    | emp2 |  176576.3432 | 
    | emp3 |  179897.7658 | 
    +------+--------------+
    3 rows in set (0.00 sec)
    
    mysql> select name, salary, ceil(salary*100)/100 from test;
    +------+--------------+----------------------+
    | name | salary       | ceil(salary*100)/100 |
    +------+--------------+----------------------+
    | emp1 | 1323243.3123 |         1323243.3200 | 
    | emp2 |  176576.3432 |          176576.3500 | 
    | emp3 |  179897.7658 |          179897.7700 | 
    +------+--------------+----------------------+
    3 rows in set (0.03 sec)
    Try and put together an UPDATE statement with what you now know and if you still have difficulties I can help out some more.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Sep 2010
    Posts
    4
    This is my attempt:

    UPDATE products_price_break SET products_price(CEIL(products_price * 100) / 100) WHERE products_price > "0.0000"

    Seems I have it wrong.
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CEIL(products_price * 100) / 100) WHERE products_price > "0.0000"' at line 1

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dford View Post
    Seems I have it wrong.
    yup, and your error occurs exactly where the error message tells you to look

    check the UPDATE syntax in the manual, and you will see what's missing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2010
    Posts
    4
    Thank you,

    I think I got it,
    UPDATE products_price_break SET products_price=(CEIL(products_price * 100) / 100) WHERE products_price > "0.0000"

    I will create a backup and test it out.

Posting Permissions

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