if else statment in sql query for new column uisng SQL?

Question

my problem is if sales.cost is < 100 then 'special' else if sales.cost => 100 'Regular'
here is my query

SELECT List.Item, List.Department, List.Group, List.Cost, [Query 2].Sales, [Query 2].Revenue , special 



FROM [Sales Units], List INNER JOIN [Query 2] ON List.Item = [Query 2].Item;

the last coloumn is addition and here is some more explainatiopn

    Special       if List.Cost

    Special      Less than 100

    Regular      100 or greater

i gooled alot but found case as a solution but if does not provide suitable answer i will be thankfull if you solve little puzzle

Here is case that i used

     SELECT List.Item, List.Department, List.Group, List.Cost, [Query 2].Sales,     [Query 2].Revenue , Special  ,

case 

  when [Query 2].Sales < 100 then Special = 'Special' 

  when [Query 2].Sales => 100 then Special = 'Regular' 

end

 FROM [Sales Units], List INNER JOIN [Query 2] ON List.Item=[Query 2].Item

ORDER BY List.Item;

Answer 1

SELECT l.Item, 
       l.Department, 
       l.Group, 
       FormatCurrency(l.Cost,2,-2,-2,-2) as Cost,
       q.Sales, 
       q.Revenue , 
       CASE WHEN l.Cost < 100 THEN 'Special'
       ELSE 'REGULAR' END AS 'Special'
FROM [List] l INNER JOIN [Query 2] q ON l.Item = q.Item;

For SQL use FORMAT(l.Cost,'C','en-us')

if else statment in sql query for new column uisng SQL?

Sign up