How to count scoring? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to count scoring?

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-23 : 21:51:29

I have a scoring query already. However, I need to count how many cases belong to each score. I was not sure how would I write a statement for that. Please see example below:

Column Name: 10 columns (Score1, Score2, Score3, Score4, Score6, Score5, Score6, Score7, Score8, Score9, Score10 and TotalScore)

Each column was range from score 10 to 100, such as:
1) Score1:
case when score1 is null then 0
when score1 >10000000 then 100
when score1 > 1000000 then 90
when score1 > 100000 then 75
when score1 > 10000 then 50
when score1 > 1000 then 25
when score1 > 0 then 10
else 0 end as Score1
Same thing for other score columns.
I already wrote case statement for 10 scored columns, and totalscore = (score1 + score2 + ...score10. But now I need to count how many cases in the below range:

1. How many cases > 199?
2. How many cases from 150 to 199
3. How many cases from 100 to 149
4. How many cases from 50 to 99
5. How many cases from 25 to 49
6. How many cases from 1 to 24
7. and how many cases <1

Can anyone help me to write this statement? Thank you very much!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 22:09:19

[code]
sum(case when score1 > 199 then 1
end) as [ > 199],
sum(case when score1 between 150 and 199 then 1
end) as [ 150 to 199]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-24 : 08:09:35

quote:
Originally posted by khtan

sum(case when score1 > 199 then 1
end) as [ > 199],
sum(case when score1 between 150 and 199 then 1
end) as [ 150 to 199]

KH
[spoiler]Time is always against us[/spoiler]

It won't work...Can I do total score like this:

case when TotalScore > 199 then '>199'
when TotalScore > 149 then '150 to 199'
when TotalScore > 99 then '100 to 149'
when TotalScore > 49 then '50 to 99'
when TotalScore > 24 then '25 to 49'
when TotalScore >=1 then '1 to 24'
else '<1' end as ScoreRange
thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 08:20:49

quote:
Originally posted by ntn104

quote:
Originally posted by khtan

sum(case when score1 > 199 then 1
end) as [ > 199],
sum(case when score1 between 150 and 199 then 1
end) as [ 150 to 199]

KH
[spoiler]Time is always against us[/spoiler]

It won't work...Can I do total score like this:

case when TotalScore > 199 then '>199'
when TotalScore > 149 then '150 to 199'
when TotalScore > 99 then '100 to 149'
when TotalScore > 49 then '50 to 99'
when TotalScore > 24 then '25 to 49'
when TotalScore >=1 then '1 to 24'
else '<1' end as ScoreRange
thanks,

you can.
b/w why the posted query didnt work for u? what was the error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 08:26:35

[code]SELECT ScoreRange,
COUNT(*)
FROM (
SELECT CASE
WHEN TotalScore > 199 THEN '>199'
WHEN TotalScore > 149 THEN '150 to 199'
WHEN TotalScore > 99 THEN '100 to 149'
WHEN TotalScore > 49 THEN '50 to 99'
WHEN TotalScore > 24 THEN '25 to 49'
WHEN TotalScore >=1 THEN '1 to 24'
ELSE '<1'
END AS ScoreRange
FROM Table1
) AS d
GROUP BY ScoreRange[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources