Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Unanswered: Simple SQL question about formatting results

    Embarrassed to ask but nevertheless here i go:

    I have a SQL like this:

    select sum(qty)
    from tblXYZ


    The results is displayed as (for example) 123456789.56


    I want the results to be displayed as 123,456,789.56 (i.e. use the currency or the comma format; whatever it's called).

    Please help

  2. #2
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    ....

    SET @Dollars = 1234.56

    Convert (varchar(8), CAST (@Dollars as money), 1)

    ....


    would turn 1234.56 into 1,234.56 i do believe...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That will work, but keep in mind that then you are dealing with a string, and not a number. Data formatting should be left to the user interface. It does not normally fall under the scope of the database server in application design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    string it is

    DECLARE @Dollars Decimal(15,2), @x varchar(25)
    SET @Dollars = 1234.56
    SELECT @x = Convert (varchar(8), CAST (@Dollars as money), 1)
    SELECT @x
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Posts
    163
    All,

    Thanks for your replies.

    Actually I have learned a few new things by goingthrough the replies but am not sure how to apply the suggested solution to my actual problem. Please bear with me :embarrassed:

    My original SQL is below:
    select sum(qty)
    from tblXYZ

    and I want the result to be displayed in comman format with 2 decimal places. How do i re-write the above SQL using the suggested convert function to get the desired result. If this helps, the datatype for my qty column is float(8).

    Thanks in advance.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you cut and paste my code in to QA and execute it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Posts
    163
    I guess we are onvery different tracks and I'm to blame for causing the confusion. Sorry about that.

    I am sure that your code works great
    DECLARE @Dollars Decimal(15,2), @x varchar(25)
    SET @Dollars = 1234.56
    SELECT @x = Convert (varchar(8), CAST (@Dollars as money), 1)
    SELECT @x
    But my request is how to use the format function in the following query to get the results displayed in comma format with 2 decimal places i.e.

    select Convert(???, ???sum(qty))
    from tblXYZ

    I hope this clarifies my requirement?

    Thanks

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    select CAST (sum(qty) as money)
    from tblXYZ

    is the simple answer.... I think,....

  9. #9
    Join Date
    Oct 2003
    Posts
    163
    Sorry but it does not work and I still am getting the results like 1234567.99

    The data type of the qty field was float and I even changed it to money. Then I tried both the following but still get the same results

    select CAST (sum(qty) as money)
    from tblXYZ

    select sum(qty)
    from tblXYZ


    Clues???

  10. #10
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    ok, here

    SELECT

    Convert (varchar(8), CAST (SUM(qty) as money), 1)


    From
    tst_table



    I created a table called tst_table, with a qty field as float(8), and the above works just fine. If it isn't working, tell us what the error is that you are getting...

Posting Permissions

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