Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in Select case with tricky logic Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 6:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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,12 as Good)

select * from result

Any sample query please. bit confused about using select case for this category
Post #1554735
Posted Tuesday, March 25, 2014 7:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 3,622, Visits: 8,129
That's not difficult, what have you tried?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554737
Posted Tuesday, March 25, 2014 7:06 PM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.

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

Thanks for your precious time on this post.
Post #1554739
Posted Tuesday, March 25, 2014 7:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 3,622, Visits: 8,129
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554741
Posted Tuesday, March 25, 2014 7:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Yes it gives proper result. that's why i marked this a resolved already

Thank you for your time Luis.
Post #1554742
Posted Tuesday, March 25, 2014 11:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 94, Visits: 45
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
Post #1554778
Posted Wednesday, March 26, 2014 4:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
thanks for your time on this pandey
Post #1554872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse