CODEWITHSUNDEEP
sql-server
S. Braun
S. Braun

Reputation: 69

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

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

Output:

Upvotes: 0

Views: 171

Answers (2)

Robert Sievers
Robert Sievers

Reputation: 1353

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

Upvotes: 1

Darkwing
Darkwing

Reputation: 7595

Pseudo code should get you there.

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

Upvotes: 2

Related Questions