Need help in Select case with tricky logic

Question

Need help in Select case with tricky logic

born2achieve

Hall of Fame

Points: 3182
More actions
March 25, 2014 at 6:43 pm

#286916

hi,
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,12as Good)
select * from result
Any sample query please. bit confused about using select case for this category

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That's not difficult, what have you tried?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 2

Hi Luis,

How are you? Since long time keeping your foot print on my post. Appreciate your time on this. Some time mind doesn't work to write easy logic.

this is how i implemented.

selectsum(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

fromsample

Thanks for your precious time on this post.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

That seems to give the correct result. What's the problem? You could add an ELSE 0 to avoid having NULLs in your data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

Yes it gives proper result. that's why i marked this a resolved already

Thank you for your time Luis.

Avi1 SSCrazy Points: 2323 More actions · Answer 5

I know this is resolved, still....

just a suggetion use a look up table or create another cte for all grades.

Then join this look up table with your main cte (or table) where score in main table is between score range in the grade lookup. this will give you more felxibility.

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 )

,

LkpGrade as (

select 'Grade1' as Grade,0 as MinScore, 39 as MaxScore union all

select 'Grade2' as Grade,40 as MinScore, 69 as MaxScore union all

select 'Grade3' as Grade,70 as MinScore, 100 as MaxScore

)

select g.Grade, sum(s.Items) from sample s

inner join LkpGrade g on s.score between g.MinScore and g.MaxScore

group by g.Grade;

hope this helps

Regards,

Avi

born2achieve Hall of Fame Points: 3182 More actions · Answer 6

born2achieve

Hall of Fame

Points: 3182

March 26, 2014 at 4:40 am

#1700216

thanks for your time on this pandey