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

Question

I am calculating the commission rate for employees and would like to sort the results by the total in descending order, but would also like to add the dollar sign in.

My current attempt is:

SELECT OrderLine.OrderID, Employee.FirstName AS "Employee First Name",
    Employee.LastName AS "Employee Last Name", 
    '$' + CONVERT(NVARCHAR, SUM((CommissionRate * ((Quantity * Price) * (1 - Discount)))), 1) AS "Total Commission"
FROM OrderLine INNER JOIN [Order] ON Orderline.OrderID = [Order].OrderID
    INNER JOIN Employee ON [Order].EmployeeID = Employee.EmployeeID
GROUP BY OrderLine.OrderID, Employee.FirstName, Employee.LastName
ORDER BY 'Total Commission' DESC

The output appears to be sorted as a string and my initial thought was to add:

ORDER BY SUM((CommissionRate * ((Quantity * Price) * (1 - Discount))))

But figured there was an easier way.

I had previously looked at the following questions on stack:

How do I format a number with commas in T-SQL?

ORDER BY for currency values

You are effectively not ordering by anything. You are ordering by the string literal 'Total Commission' which means there is no specified order. I would also strongly suggest not adding a '$' to your values. That is a presentation aspect and should be done in the application layer. You also should not use "column names". If you really want spaces (not recommended) use [Column Name] — Sean Lange, May 21 '19 at 13:29
Put the formatting of the value in your presentation layer, not in the SQL. Also, at the moment, you are ordering by the **literal string** `'Total Commission'`. — Thom A, May 21 '19 at 13:29
Might also want to take a peek [here](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). Always specify the length of (n)varchar — Sean Lange, May 21 '19 at 13:37

score 2 · Accepted Answer · answered May 21 '19 at 13:32

2

Pseudo code should get you there.

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

answered May 21 '19 at 13:32

Matt Evans

7,113
7
32
64

score 1 · Answer 2 · answered May 21 '19 at 13:35

I am a big fan of using common table expressions. Like so...

WITH CTE AS (
    SELECT OrderLine.OrderID, Employee.FirstName AS "Employee First Name",
    Employee.LastName AS "Employee Last Name", 
    CONVERT(NVARCHAR, SUM((CommissionRate * ((Quantity * Price) * (1 - Discount)))), 1) AS "Total Commission"
    FROM OrderLine INNER JOIN [Order] ON Orderline.OrderID = [Order].OrderID
    INNER JOIN Employee ON [Order].EmployeeID = Employee.EmployeeID
GROUP BY OrderLine.OrderID, Employee.FirstName, Employee.LastName
)

SELECT 
    CTE.[Employee First Name], 
    CTE.[Employee Last Name], 
    '$' + CTE.[Total Commission] as Total
FROM CTE
ORDER BY CTE.Total Commission DESC

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

2 Answers2