1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL help to format decimal places

Discussion in 'MySQL Forums' started by JosephK, Aug 9, 2011.

  1. JosephK

    JosephK Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I am learning MySQL database. I have a simple question. I have a query which returns a number, some thing like this

    Code (SQL):
    SELECT salary FROM employee
    WHERE salary > 10000
    I get the answer as 15000.0000 which is 4 decimal places

    What can I change in my sql query to make it 2 decimal places

    Thank You.
     
  2. Necromancer

    Necromancer Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    This is very simple.

    Code (SQL):
    mysql> SELECT ROUND(15000.0000,2);
    +----------------------+
    | ROUND(15000.0000,2) |
    +----------------------+
    |           15000.00        |    
    +----------------------+
    Alternately you can also use truncate(N,D) to format decimal places.

    Do remember though that the difference between round and truncate is that while round makes an arithmetic rounding off, truncate simply truncates the result to the specified number of decimal places.
     
  3. jonmax

    jonmax Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Dubai UAE
    i am also Lerner it is also good for me thanks for answering.
     
  4. davyjones

    davyjones Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    There are many functions for formatting decimal points like round said by Necromancer. I given below the details of the methods.
    Truncate
    Format
    Hope it helps. For more details contact connected commerce
     
  5. oliviathomas

    oliviathomas Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    london
    try this
    SELECT ProductName, ROUND(Price,1) AS RoundedPrice
    FROM Products;