Order By with a Case on an aliased aggregate column in SQL Server not working

Question

Objectives

I would like to pass TopOrWorst and date ranges as parameters into a stored procedure. It should return Top Or Worst 10 products based on the sum of sales values between the dates range provided.

I have hardcoded the date ranges in the query and create a variable @TopOrWorst for simplicity.

Below are 3 different queries, out of which latter 2 works, but I wanted the 1st one to work for me.

DECLARE @TopOrWorst INT = 1; -- 1 = Top, 2= Worst

-- Query #1: sorting DOES NOT work when the column alias 
-- is used inside the case statement.
SELECT TOP 10 
    sh.Stockcode,
    SUM(sh.SalesValue) AS TotalSales 
FROM 
    SalesHistory sh 
WHERE 
    DateSold BETWEEN '2017-05-05' AND '2017-05-10'
GROUP BY 
    sh.Stockcode
ORDER BY 
    CASE WHEN @TopOrWorst = 1 THEN TotalSales END DESC,
    CASE WHEN @TopOrWorst = 2 THEN TotalSales END  

-- Query #2: sorting works when then column is SUMed inside the case statement.

SELECT TOP 10 
    sh.Stockcode,
    SUM(sh.SalesValue) AS TotalSales 
FROM 
    SalesHistory sh 
WHERE 
    DateSold BETWEEN '2017-05-05' AND '2017-05-10'
GROUP BY 
    sh.Stockcode
ORDER BY 
    CASE WHEN @TopOrWorst = 1 THEN SUM(sh.salesvalue) END DESC,
    CASE WHEN @TopOrWorst = 2 THEN SUM(sh.salesvalue) END  

-- Query #3: sorting works with the Alias field without the case statement.
SELECT TOP 10 
    sh.Stockcode,
    SUM(sh.SalesValue) AS TotalSales 
FROM 
    SalesHistory sh 
WHERE 
    DateSold BETWEEN '2017-05-05' AND '2017-05-10'
GROUP BY 
    sh.Stockcode
ORDER BY 
    TotalSales DESC  

What is wrong with query #1 ? Appreciate all the help in advance.

Thanks

question from:https://stackoverflow.com/questions/65832687/order-by-with-a-case-on-an-aliased-aggregate-column-in-sql-server-not-working

Answer 1

SQL Server allows aliases to be used in ORDER BY -- all alone.

SQL Server does not allow aliases to be part of an expression. Something as simple as TotalSales + 1 won't work either.