FORMAT integer with thousand comma separator so I can use ORDER BY for the result (SQL SSMS)

The following code:

SELECT      LGA,        
            FORMAT(SUM(Total_2019)+0,'00,000') as Total2019
FROM        Crime_LGA
GROUP BY    LGA
ORDER BY    Total2019 DESC;

Gives the correct result but the formatting gives unwanted 0s as the numbers decline, for example 09,591 instead of 9,591 (shown in image below)

also tried the following code:

SELECT      LGA,        
            FORMAT(SUM(Total_2019),'N0') as Total2019
FROM        Crime_LGA
GROUP BY    LGA
ORDER BY    Total2019 DESC;

but the number is converted to NVARCHAR, which means the result is not sorted.

The Total_2019 column is INT in the table. I’ve searched around but all solutions I’ve found have resulted in converting the answer to NVARCHAR which means the result is out of order.

Using SSMS. Appreciate your time.

Answer

I the second query you posted returns the expected values and the problem is just with the order, then you can order on the sum of the original column (instead of ordering on the converted sum):

SELECT      LGA,        
            FORMAT(SUM(Total_2019),'N0') as Total2019
FROM        Crime_LGA
GROUP BY    LGA
ORDER BY    SUM(Total_2019) DESC;

Advertisement

Answer