Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL Cast the sum as Money

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 : enter image description here

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

like image 640
JohnZ Avatar asked Mar 20 '13 17:03

JohnZ


People also ask

How do you CAST money in SQL?

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.

How do I display a SUM in SQL?

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.

Can you use SUM in SQL?

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.

What is CAST () and convert () functions in SQL Server?

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.


2 Answers

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

like image 146
Taryn Avatar answered Sep 29 '22 11:09

Taryn


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
like image 44
Jamie Le Tual Avatar answered Sep 29 '22 11:09

Jamie Le Tual