February 2, 2014 at 7:54 pm
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
February 2, 2014 at 8:12 pm
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.
February 2, 2014 at 10:11 pm
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
Change is inevitable... Change for the better is not.
February 3, 2014 at 6:11 am
Hi Jeff,
Yes, it really easy and straight forward solution. Good learning for me today. Thank you so munch for mentoring me.
February 3, 2014 at 7:28 am
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply