Convert Numbers into Comma Separated Numbers and sort numerically-CodePudding

I am getting hourly sales for the last 3 months

USE [SambaPos]
GO
SELECT
    DATEPART(HOUR, [LastUpdateTime]) AS [Hour], 
    FORMAT(SUM([TotalAmount]), 'N0') AS [Sales]
FROM
    Tickets
WHERE
    [LastUpdateTime] > 
    DATEADD(MONTH, 
        DATEDIFF(MONTH, 0, 
            DATEADD(MONTH, -3, GETDATE())
        ), 0
    ) 
    AND
    [LastUpdateTime] < GETDATE()
GROUP BY
    DATEPART(HOUR, [LastUpdateTime])
ORDER BY
    [Sales] DESC

Using Format function formats the numbers in a string and order by on a string column doesn't give numerically sorted numbers. So, how do I preserve the numeric sorting along with string formatting.

Here are the results from string sorted sales, I want them to be numerically sorted by represented in comma separated format.

Thanks

CodePudding user response：

You should be able to use the raw value in the ORDER BY clause.

Try changing your query to the following

USE [SambaPos]
GO
SELECT
    DATEPART(HOUR, [LastUpdateTime]) AS [Hour], 
    FORMAT(SUM([TotalAmount]), 'N0') AS [Sales]
FROM
    Tickets
WHERE
    [LastUpdateTime] > 
    DATEADD(MONTH, 
        DATEDIFF(MONTH, 0, 
            DATEADD(MONTH, -3, GETDATE())
        ), 0
    ) 
    AND
    [LastUpdateTime] < GETDATE()
GROUP BY
    DATEPART(HOUR, [LastUpdateTime])
ORDER BY
    SUM([TotalAmount]) DESC