2
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?

Uncle_Moris
  • 83
  • 4
  • This is fine. You could also have a derived table (subquery in FROM), where you do the price calculation. – jarlh May 02 '22 at 09:04

1 Answers1

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;
JohanB
  • 346
  • 2
  • 10