How to add conditional search conditions in a SQL report

Question

I am working with the Northwinds Trader database for an SQL question and trying to print a report containing the OrderID and Freight cost of orders. Problem being that this table must show the Freight cost with a 20% tax if the cost is >= $500. How would I write a query that would display Freight as Freight + (Freight * .20) if Freight >= 500?

My current query is as follows:

SELECT OrderID, FreightCost = Freight + (Freight * .20) 
FROM Orders 
WHERE Freight >= 500 

Answer 1

You can use CASE statement to get the expected results.

When Freight >= 500 then compute the cost to show 20% tax else no change

SELECT  [OrderID]
  ,[Freight]
  ,case when Freight >= 500 then Freight + (Freight * 0.20) else convert(float,Freight) end as FreightCost
FROM [dbo].[Orders]
order by 2 desc --using order by to show the results