Conversion failed when converting the nvarchar value ‘$28,926.25’ to data type int

USE AdventureWorks2014

SELECT 
    result.BusinessEntityID, 
    p.FirstName, p.LastName, 
    result.TotalSales, '2011' AS SalesYear, 
    CASE 
        WHEN CAST(result.TotalSales AS int) > 1000000 THEN 'MillionPlus'
        WHEN CAST(result.TotalSales AS int) < 100000 THEN 'Warning'
        ELSE 'Met Expectations'
    END AS BonusCategory
FROM 
    person.Person AS P, 
    (SELECT sp.BusinessEntityID, FORMAT(sum(soh.SubTotal), 'c', 'en-us') AS TotalSales 
     FROM sales.SalesPerson as sp 
     RIGHT JOIN sales.SalesOrderHeader as SOH ON sp.BusinessEntityID = soh.SalesPersonID
     WHERE soh.orderdate LIKE '%2011%'
     GROUP BY sp.BusinessEntityID) AS result 
WHERE 
    result.BusinessEntityID = p.BusinessEntityID 
    OR result.TotalSales IS NULL
ORDER BY 
    result.BusinessEntityID 

Full error message

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value ‘$28,926.25’ to data type int.

Answer

Something to understand when programming on almost ANY platform is converting between number values like 123.45 and string or text values like "123.45" is a surprisingly slow and expensive operation. They may look the same, but those are different values, and largely thanks to internationalization/cultural concerns the conversions between them are not trivial. Always seek to minimize these conversions, and when you do need them leave it until the last possible moment.

SQL Server is no exception here. It can be very picky about mixing between numbers and text.

For this question, the nested inner query takes the sum(soh.SubTotal) expression, which is a number, and wraps it in a FORMAT() function call, which creates a text field. We later try to use the result in the outer query as if it were a number again.

Don’t do that!

Wait to format the result until the SELECT clause of the outer statement, at the last possible moment. This allows us to remove the CAST()s from inside the CASE expression completely.

Also, NEVER use the old A,B WHERE join syntax. It’s been out of date for more than 25 years now.

USE AdventureWorks2014
SELECT result.BusinessEntityID, p.FirstName, p.LastName, 
    FORMAT(result.TotalSales,'c','en-us') As TotalSales, '2011' as SalesYear, 
    CASE 
        WHEN result.TotalSales > 1000000 THEN 'MillionPlus'
        WHEN result.TotalSales < 100000 THEN 'Warning'
        ELSE 'Met Expectations'
    END as BonusCategory
FROM person.Person as P
INNER JOIN (
       SELECT sp.BusinessEntityID, FORMAT(sum(soh.SubTotal),'c','en-us') as TotalSales 
       FROM sales.SalesPerson as sp 
       RIGHT JOIN sales.SalesOrderHeader as SOH 
            ON sp.BusinessEntityID = soh.SalesPersonID
       WHERE soh.orderdate LIKE '%2011%'
       GROUP BY sp.BusinessEntityID
    ) result ON (result.BusinessEntityID = p.BusinessEntityID OR result.TotalSales is null)
ORDER BY result.BusinessEntityID 

Even better, don’t even convert to a string/text in the SELECT clause! Let the client program or reporting tool handle this part. This is another way we can follow the “leave the conversion to the last possible moment” guidance in the first paragraph.