Need help in Select case with tricky logic

  • 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

  • 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
  • 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.

  • 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
  • Yes it gives proper result. that's why i marked this a resolved already

    Thank you for your time Luis.

  • 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

  • thanks for your time on this pandey

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

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