sqlazure-sql-databasenorthwind

How to add conditional search conditions in a SQL report


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 

Solution

  • 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
    

    enter image description here