sql - Using CAST in ORDER BY references sub query instead of main query

Question

I'm trying to run a query on RedShift that takes the results of a subquery, buckets them into groups, and then shows the count for the groups in ascending order

WITH MonthlyAmountPerUser as ( 
    SELECT Month, UserId, SUM(Amount) as TotalAmount FROM AnotherTable
    GROUP BY UserId, Month
)
    
SELECT
    CASE 
        WHEN "TotalAmount" > 0 AND "TotalAmount" <= 500 THEN '0 - 500'
        WHEN "TotalAmount" > 500 AND "TotalAmount" <= 1000 THEN '500 - 1000'
        WHEN "TotalAmount" > 1000 AND "TotalAmount" <= 1500 THEN '1000 - 1500'
        WHEN "TotalAmount" > 1500 AND "TotalAmount" <= 2000 THEN '1500 - 2000'
        ELSE '20000 +'
    END AS MonthlyRange,
    count(*) AS MonthlyCount

FROM MonthlyAmountPerUser  
    
GROUP BY MonthlyRange
ORDER BY MonthlyRange ASC

This gives the correct results however, the "MonthlyRange" column is ordered as a string which ends up with the ordering

0 - 500
1000 - 1500
1500 - 2000
20000 +
500 - 1000

To get this to order correctly this I'm trying to cast the "MonthlyRange" column to a number which I would expect to change the ordering to

0 - 500
500 - 1000
1000 - 1500
1500 - 2000
20000 +

However when I change the ORDER BY statement to

ORDER BY CAST(MonthlyRange as INT) ASC 

I get the error

Invalid operation: column "monthlyrange" does not exist in monthlyamountperuser

Why does RedShift try to look for a column inside of the subquery when I try to modify the column in the ORDER BY statement? How can I get it to reference the column that exists in my main query?

question from:https://stackoverflow.com/questions/65847205/using-cast-in-order-by-references-sub-query-instead-of-main-query

Answer 1

You will need another case..when expression as follows:

ORDER BY CASE 
        WHEN  MonthlyRange = '0 - 500' then  1
        WHEN  MonthlyRange = '500 - 1000' then  2
        WHEN  MonthlyRange = '1000 - 1500' then  3
        WHEN  MonthlyRange = '1500 - 2000' then  4
        ELSE 5
    END