Can i use 'alias' AS in CASE Statement PostgreSQL

Question

SELECT 
    id
    , quantity
    , unit_price 
    , (antity * unit_price) as price    
CASE
    WHEN quantity * unit_price > 5000 THEN (quantity * unit_price) * 0.2
    WHEN quantity * unit_price > 3000 THEN (quantity * unit_price) * 0.15 
    ELSE null 
END 

AS discount

FROM OrderDetails;

I tried to use the alias price in CASE but it doesn't work. In the above code, I repeated quantity * unit_price 5 times. Do exist any better way to realize this code? Is there way to avoid repetition in the CASE statement?

Answer 1

You could use a CTE

WITH CTE as (
SELECT 
    id
    , quantity
    , unit_price,
    (antity * unit_price) as price
FROM OrderDetails
)
SELECT 
    id
    , quantity
    , unit_price 
    , price    
CASE
    WHEN price > 5000 THEN price * 0.2
    WHEN price > 3000 THEN price * 0.15 
    ELSE null 
END 

AS discount

FROM OrderDetails;