Using SQL Server 2008, I am trying to get the return of some of these columns to come back as $xxx,xxx.xx
This is the query that I am using (this query then some updates just to calculate the numbers and select ##tempshow
at the end)
SELECT
CASE GROUPING(s.StoreID) WHEN 1 THEN '' ELSE s.StoreID END [StoreID],
CASE GROUPING(p.VendorID) WHEN 1 THEN '' ELSE p.VendorID END [VendorID],
SUM(d.Quantity) AS [UnitSold],
CAST(SUM(d.amount * d.quantity) AS DECIMAL(18,2)) AS Amount,
CAST(SUM(d.Discount) AS DECIMAL(18,2)) AS Discount,
CAST(SUM((d.Amount * d.Quantity - d.Discount)) AS DECIMAL(18,2)) AS ExtSold,
CAST(SUM(d.Cost * d.Quantity) AS DECIMAL(18,2)) AS Cost,
CAST(0 AS DECIMAL(18,2)) AS Profit,
CAST(0 AS DECIMAL(18,2)) AS OnHand,
CAST(0 AS DECIMAL(18,2)) AS OnHandRetail,
CAST(0 AS DECIMAL(18,2)) AS OnHandCost,
CAST(0 AS DECIMAL(18,2)) AS ReceivedCost,
CAST(0 AS DECIMAL(18,2)) AS ReceivedRetail,
CAST(0 AS DECIMAL(18,2)) AS ReceivedQty,
CAST(0 AS DECIMAL(18,2)) AS Margin,
CAST(0 AS DECIMAL(12,2)) AS TurnOver,
CAST(0 AS INTEGER) AS OnOrder
INTO
##tempshow
FROM
RPTrs s,
RPTrsd d,
RPIv i,
RPProducts p
WHERE
s.ReceiptNO = d.ReceiptNO and
s.StoreID = d.StoreID and
i.UPC = d.UPC and
i.StoreID = d.StoreID and
p.ProductID = i.IVProduct and
s.StoreID = '01' and
s.TRSDate > GETDATE()-20 and
p.Service = 0
GROUP BY
GROUPING SETS((s.StoreID,p.VendorID),())
Which returns :
I have tried
CAST(SUM(d.amount * d.quantity) AS MONEY) AS Amount,
and
SUM(CAST((d.amount * d.quantity) AS MONEY)) AS Amount,
Expected output (plus the other columns same as this Amount
column):
|StoreID | VendorID | UnitSold | Amount
---------------------------------------------
1 | 01 | 0000 | 0 | $0.00
2 | 01 | am | 62 | $6,275.00
3 | 01 | AO | 58 | $18,964.00
4 | 01 | payless | 6 | $1,383.36
5 | | | 126 | $26,622.36
I need the Amount, Discount, ExtSold, Cost, Profit, OnHandRetail, OnHandCost, ReceivedCost, ReceivedRetail
to be in the money format
In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency. The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument.
Example - With Single ExpressionSELECT SUM(salary) AS "Total Salary" FROM employees WHERE salary > 25000; In this SQL SUM Function example, we've aliased the SUM(salary) expression as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.
You can use it to add all the values in one column across all rows in a table, to total the results of an expression that uses more than one column, and to sum up values for a group of rows. You can also use SUM() inside the HAVING clause to filter data according to the summed values.
The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
This is something that should be done on the presentation layer, but if you need to do this in sql you can use:
'$'+convert(varchar(50), CAST(amount as money), -1) amount
Here is an example:
;with cte (amount)
as
(
select 123254578.00 union all
select 99966.00 union all
select 0.00 union all
select 6275.00 union all
select 18964.00 union all
select 1383.36 union all
select 26622.36
)
select '$'+convert(varchar(50), CAST(amount as money), -1) amount
from cte
See SQL Fiddle with Demo. This returns:
| AMOUNT |
-------------------
| $123,254,578.00 |
| $99,966.00 |
| $0.00 |
| $6,275.00 |
| $18,964.00 |
| $1,383.36 |
| $26,622.36 |
Note: This will be much easier in SQL Server 2012 because you can use FORMAT()
;with cte (amount)
as
(
select 123254578.00 union all
select 99966.00 union all
select 0.00 union all
select 6275.00 union all
select 18964.00 union all
select 1383.36 union all
select 26622.36
)
select '$'+FORMAT(amount,'#,0.0000') amount
from cte
See SQL Fiddle with Demo
Adding to bluefeet's answer, the FORMAT function available in SQL 2012 could be done like this:
SELECT FORMAT(12345.6789, 'C', 'en-us')
The C means currency, and the last argument is culture. The culture is important if you want your application to be multilingual, because it takes care of things like dollar (or euro) signs, and the thousands separator. For instance:
SELECT
FORMAT(12345.6789, 'C', 'en-us') as "USA",
FORMAT(12345.6789, 'C', 'fr-ca') as "French Canada",
FORMAT(12345.6789, 'C', 'fr-fr') as "French France",
FORMAT(12345.6789, 'C', 'hi-in') as "Hindi India"
Will give this result:
USA French Canada French France Hindi India
----------- ------------- -------------- --------------
$12,345.68 12 345,68 $ 12 345,68 € ₹ 12,345.68
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With