CODEWITHSUNDEEP
sqlsql-server
Ozan Ayten
Ozan Ayten

Reputation: 1473

SQL Server : Conditional Column on Select query

I need assistance for my select query. I want to have 3 conditions that depends on sum(sd.Price * sd.Quantity). Like, if sum is between 0-100 types bad, between 100-200 types o.k or bigger then 200 then types good to SaleState virtual column.

Thing is we can of course work around this but what I want is, doing it within this select query. I know it's doable, I just don't remember(I did it before) and couldn't find a solution within my searches.

My data tables are :

Query:

select 
    p.ProductName, sum(sd.Price*sd.Quantity) as Total,
    (select 'good' where Total > 100) as SaleState
    -- We need something here that haves 3 condition.
from 
    Products p
left join 
    [Sales Details] sd on p.ProductID = sd.ProductID
group by 
    p.ProductName

Upvotes: 1

Views: 5009

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

select 
productname, total, 
case when total < 100 then 'Bad' 
     when total >= 100  and total < 200 then 'OK'
     when total > 200 then 'Good'
end as salestate
from
(       
select 
p.ProductName, sum(sd.Price*sd.Quantity) as Total
from Products p
left join [Sales Details] sd on p.ProductID = sd.ProductID
group by p.ProductName
) t

You can use case statement to assign values based on the sum in the query.

Upvotes: 2

Related Questions