Search code examples
sqlsql-order-by

Convert Numbers into Comma Separated Numbers and sort numerically

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.

enter image description here

Thanks

Solution

  • 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