Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rounding to 2 decimals with $ sign

Author  Topic 

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-06-05 : 18:13:19
I am using the AdventureWorks2012 database. Here is my code:

SELECT SalesOrderID, SUM(UnitPrice) AS Price
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

This runs just fine. But the alias column called Price, contains digits going out to 4 places. I want to round to the 2nd decimal place with a $ sign in front so that these numbers take on currency format.

I tried using the CAST function and it is not working.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-06-05 : 19:43:55
cast(SUM(UnitPrice) as decimal(10, 2)) should get you the value. If you want to format the number with dollar signs and commas, etc., you should do that at the presentation level.

=================================================
May my silences become more accurate. -Theodore Roethke (1908-1963)
Go to Top of Page

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-06-18 : 13:10:17
quote:
Originally posted by Bustaz Kool

cast(SUM(UnitPrice) as decimal(10, 2)) should get you the value. If you want to format the number with dollar signs and commas, etc., you should do that at the presentation level.

=================================================
May my silences become more accurate. -Theodore Roethke (1908-1963)



Thank you for your reply. I'm not sure what you mean by the presentation level.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 14:09:27
What Bustaz Kool is suggesting is that the type of formatting (prefixing it with the currency symbol, including thousands separators etc.) are better done outside of SQL Server. For example, if you are presenting the data in a web page, .Net/C# has a number of facilities to format currencies, dates etc. to your liking, including taking care of internationalization. Similarly, if you are using SQL Server Reporting Services, that again has similar features. I am not familiar with Crystal Reports or other reporting tools, but I can't imagine that they don't have similar features.
Go to Top of Page

shan007
Starting Member

17 Posts

Posted - 2013-06-18 : 14:23:34
I think this should resolve your request. Let me know if it helps. I'm not sure about your purpose. Still I'd like to let you know the solution that you are expecting.

SELECT SalesOrderID, '$' + CAST(ROUND(SUM(UnitPrice),2) as nvarchar(10)) AS Price
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

==============================
I'm here to learn new things everyday..
Go to Top of Page
   

- Advertisement -