using count with select case

  • Hi,

    Table Name : Mark

    Sample data:

    select 20 as Mark

    union all

    select 30 as Mark

    union all

    select 70 as Mark

    union all

    select 67 as Mark

    union all

    select 42 as Mark

    union all

    select 49 as Mark

    union all

    select 60 as Mark

    union all

    select 56 as Mark

    union all

    select 55 as Mark

    required output:

    Text Count

    Mark(<39) 2

    Mark(between 40 and 69) 6

    Mark(>=70) 1

    i want to get the mark data count based on the range of values. Any sample Query please

  • This is how i achieved now.

    with Marks as(

    select 20 as score

    union all

    select 30 as score

    union all

    select 70 as score

    union all

    select 67 as score

    union all

    select 42 as score

    union all

    select 49 as score

    union all

    select 60 as score

    union all

    select 56 as score

    union all

    select 55 as score)

    select CASE WHEN score >0 AND score <=39

    THEN '0<score<=39'

    WHEN score >39 AND score <=69

    THEN '39<score<=69'

    WHEN score >=70

    THEN 'score >=70'

    END as Text

    ,COUNT(score)

    from [Marks]

    GROUP BY CASE WHEN score >0 AND score <=39

    THEN '0<score<=39'

    WHEN score >39 AND score <=69

    THEN '39<score<=69'

    WHEN score >=70

    THEN 'score >=70'

    END;

    would like to know is this good way or is there any better way to do this.

  • Basically, you're trying to do formatting and calculations at the same time and that's causing a really nasty sort if you look at the execution plan not to mention a formating calculation or two on every row (Hidden RBAR, of sorts). Even though you're working in a database, you still have to think "Presentation Layer, Business Layer, and Data Layer" and keeping the 3 mostly separate.

    The following separates the necessary calculations into the data layer in the "ctePreAgg" and then formats the 3 row result using the CROSS APPLY rather than trying to format all of the rows at the same time the calculation is being made. The fewer rows you have to format, the faster things will be.

    Peter Larsson has a name for this. It's called "pre-aggregation".

    Here's the code.

    with Marks as(

    select 20 as score

    union all

    select 30 as score

    union all

    select 70 as score

    union all

    select 67 as score

    union all

    select 42 as score

    union all

    select 49 as score

    union all

    select 60 as score

    union all

    select 56 as score

    union all

    select 55 as score)

    ,ctePreAgg AS

    (

    SELECT Col1 = SUM(CASE WHEN score >0 AND score <=39 THEN 1 ELSE 0 END)

    ,Col2 = SUM(CASE WHEN score >39 AND score <=69 THEN 1 ELSE 0 END)

    ,Col3 = SUM(CASE WHEN score >=70 THEN 1 ELSE 0 END)

    FROM Marks

    )

    SELECT ca.ScoreRange, ca.ScoreCount

    FROM ctePreAgg

    CROSS APPLY (SELECT '0<score<=39' ,Col1 UNION ALL

    SELECT '39<score<=69',Col2 UNION ALL

    SELECT 'score >=70' ,Col3) ca (ScoreRange,ScoreCount)

    ;

    If you don't mind this going horizontal instead of vertical, try this...

    with Marks as(

    select 20 as score

    union all

    select 30 as score

    union all

    select 70 as score

    union all

    select 67 as score

    union all

    select 42 as score

    union all

    select 49 as score

    union all

    select 60 as score

    union all

    select 56 as score

    union all

    select 55 as score)

    SELECT [0<score<=39] = SUM(CASE WHEN score >0 AND score <=39 THEN 1 ELSE 0 END)

    ,[39<score<=69] = SUM(CASE WHEN score >39 AND score <=69 THEN 1 ELSE 0 END)

    ,[score >=70] = SUM(CASE WHEN score >=70 THEN 1 ELSE 0 END)

    FROM Marks

    ;

    As you can see, all I did was get rid of the CROSS APPLY, change the names of the columns, and unencapsulate the ctePreAgg.

    As you can also see, I didn't use a GROUP BY in any of the code examples above. That's what helps avoid the SORT not to mention making the code a whole lot easier.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

    Yes, it really easy and straight forward solution. Good learning for me today. Thank you so munch for mentoring me.

  • Absolutely my pleasure. Thanks for posting readily consumable data that actually worked. You made it really easy for me to concentrate on an alternate solution instead of having to build possibly incorrect data. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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