Engr Nouman Engr Nouman - 1 month ago 6
SQL Question

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

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
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')

Comments