Need help in Select case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help in Select case

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-25 : 20:46:49

Having trouble some using select case for the below logic.


with sample as ( 
   
  select 6 as Items,0 as score union all
   select 2 as Items,30 as score union all
    select 10 as Items,50 as score union all
     select 12 as Items,75 as score )
     
     select * from sample;

i want to segregate and sum the value as three category


score >= 0 and <40
score >= 40 and <70
score >=70

[on the sample data i provided, two items meets my first condition so sum the value and displayed as 6]

Expected result:


 
 With Result as (    
     select 8 as poor, 10 as average,12	as Good)
     
     select * from result

Any sample query please. bit confused about using select case for this category

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-25 : 20:56:07

[code]select sum(case when score >= 0 and score < 40 then Items end) as poor,
sum(case when score >= 40 and score < 70 then Items end) as average,
sum(case when score >= 70 then Items end) as Good
from sample[/code]

KH
[spoiler]Time is always against us[/spoiler]