At work I'm creating a simple report - How do I retrieve a number and for the output - add a $ - Dollar Sign - on to the front of it?
Here is my query:
SELECT SUM (offer_amt) from offer where offer_id = main_table_id and offer_time_period = '202011' and offer_amt > '0') as Total_Offer
If the number returns is 10899 - I'd like the output to show as $10,899 (comma would be great but not as important as the $ ).
I'm a beginner so any help is appreciated!!!
Hi Jason and welcome!
First of all, your query is going to throw an error at least about the "main_table_id" not existing. You should also get into the seriously good habit of using table names in the 2-part form of schema_name.Table_Name. You should also get into the habit of using "table aliases" on the tables and columns, especially when there's a JOIN of any kind. And, last but not least, you should use JOIN (or whatever type of join you need) rather than the old method of joining the tables with formulas in the WHERE clause.
Using the code you posted (which is also missing some columns from the main_table), this is what it should look like (including some very readable formatting)...
SELECT Total_Offer = SUM(ofr.offer_amt)
FROM dbo.offer ofr
JOIN dbo.main_table main
ON ofr.offer_id = main.main_table_id
AND ofr.offer_time_period = '202011'
AND ofr.offer_amt > '0'
; --<---- And always end a query with a semi-colon.
Like I tell people, "Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty"!
Ok... on to your problem...
The first rule of thumb in any programming language is to at least keep the Data and Presentation layers separate. That means that formatting should "never" be done in SQL.
Since that doesn't always work because sometimes there is no separate Presentation layer, we end up having to do it in SQL.
There are two ways to do it in SQL... the easy but very slow way and the not so easy but nasty fast way. Using the FORMAT function is the easy but very slow way. I mean seriously slow compared to the more complicated fast way. (using simplified queries for examples) Of course, that won't show up here because the FORMAT function is being used against just one value instead of a million and so my urging may seem totally ridiculous to you and everyone else that reads this.
--===== Here's the easy but CPU way.
-- I strongly recommend that you NEVER use the FORMAT funtion!
SELECT Total_Offer = FORMAT(SUM(ofr.offer_amt),'C0')
FROM dbo.offer ofr
;
... and here's the fast way...
--===== Here's the super fast way.
-- This uses 25 times less CPU (it won't look like it when you print to screen).
-- Yes, FORMAT sucks that bad!
SELECT Total_Offer = CONCAT(IIF(SUM(ofr.offer_amt)<0,'(',''),'$',LTRIM(LEFT(CONVERT(CHAR(23),CONVERT(MONEY,ABS(SUM(ofr.offer_amt))),1),20)),IIF(Amt<0,')',''))
FROM dbo.offer ofr
;
As a newbie to SQL, I seriously hope that you'll write fast code instead of just code that works. That means always practicing doing it right instead of doing it easy. 
You could simplify @JeffModen's code to remove the check for negative values - because you are already removing negative values from the results. With that said, having the code able to check for negative vs positive is a good thing to get in the habit of using anyways.
We can also simplify the formula - well, maybe not simplify but just a little different:
--===== Here's the super fast way.
-- This uses 25 times less CPU (it won't look like it when you print to screen).
-- Yes, FORMAT sucks that bad!
SELECT Total_Offer = QUOTENAME(CONCAT('$',LTRIM(LEFT(CONVERT(CHAR(23),CONVERT(MONEY,ABS(SUM(ofr.offer_amt))),1),20))),IIF(SUM(ofr.offer_amt)<0,'(',''))
FROM dbo.offer ofr
;
This also addresses the issue in the original code - since there isn't a column named 'Amt' and what was supposed to be there is SUM(ofr.offer_amt)<0.
QUOTENAME has a nice feature where passing a bracket, parenthesis or curly-braces it knows to close it with the appropriate closing character.