Adding $ Dollar Sign on My Total Cost in SQL Server

Adding a dollar sign in front of currency

There are way more subqueries than are needed.

Also, if the name column in fruit is not unique, the query has the potential to throw a "too many rows" error.

If name='apple' can match more than one row in fruit, we might want something like this:

 SELECT f.fruitid
      , f.name
      , SUM( f.price * i.quantity ) AS `total value`
      , CONCAT('$', SUM( f.price * i.quantity ) ) AS `dollar total value`
   FROM fruit f
   LEFT
   JOIN inventory i
     ON f.fruitid = i.fruitid
  WHERE f.name = 'apple'
  GROUP
     BY f.fruitid 

If we want a combined value of all fruit that match, then something like this:

 SELECT SUM( f.price * i.quantity ) AS `total value`
      , CONCAT('$', SUM( f.price * i.quantity ) ) AS `dollar total value`
   FROM fruit f
   LEFT
   JOIN inventory i
     ON f.fruitid = i.fruitid
  WHERE f.name IN ('apple','pear','pineapple')

---

This doesn't address formatting to two decimal places. If we want a formatted value to two decimal places and including thousands separators, we could make use of the MySQL FORMAT function.

append currency symbol to result of sql query

you can concatenate it on your projection statement,

In MySQL,

SELECT PayerDate, CONCAT('$', PaymentAmount) PaymentAmount
FROM Payments

In SQL Server,

SELECT PayerDate, '$' + CAST(PaymentAmount AS VARCHAR(15)) PaymentAmount
FROM   Payments

Adding a dollar to numbers using CONVERT(), but still sorting as a number

Pseudo code should get you there.

SELECT 
    '$' + CONVERT(NVARCHAR, <value>) AS 'Total Commission'
FROM table
order by <value> desc

SUM of amounts with Dollar sign

You are probably better off using MySQL's implicit conversion:

select sum(trim(replace(col, '$', '')) + 0.0)

The silent conversion will include cents. In addition, non-numeric characters are after the number will not cause an error. The trim() will remove leading spaces which could also affect conversion.

Format Currency into millions of dollars SQL Server

The FORMAT function has a way of trimming the thousands

each comma reduces the displayed value by 1000

e.g.

select format(3000000,'$0,,,.000B')
select format(3000000,'$0,,M')
select format(3000000,'$0,K')

(note that I had to use decimals to show 3 million in Billions)

Output:

$0.003B

$3M

$3000K

SQL calculate total price

Your way of designing table is not good.

**Item table:**

    product_id | product_name | price
    __________________________________
    1          | Some1        | 20
    2          | Some2        | 30
    3          | Some3        | 40

    **sale table :**

    sale_id  |  product_id | quantity 
    __________________________________
    1        | 2           | 2
    2        | 1           | 1
    3        | 3           | 2

Now apply query:

SELECT sale.quantity*item.price as TOTAL FROM item,sale WHERE item.product_id=sale.product_id;

if you have more columns, then you can apply more filters.

TSQL Cast the sum as Money

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

Re-formatting values to remove dollar signs and commas in SQL server

Assuming you are using at least SQL Server 2012, you can use TRY_PARSE()

SELECT 
TRY_PARSE('-$5,000' AS MONEY), 
TRY_PARSE('$5,000' AS MONEY)

The '$-' won't parse correctly because it is nonsense. Do a replace on that one and you should be good:

SELECT 
CASE 
  WHEN val = '$-' THEN 0 
  ELSE TRY_PARSE(val AS MONEY) 
  END AS formatted_money

---

Related Topics