January 23, 2009 at 1:17 pm
Hello guys
I have a table with two columns:
Division
Score
Now I need to create the query to get result like this:
Division NumOfScoreBetween0-1000 NumOfScoreBetween1000-2000 NumOfScoreGreater2000
How do I write the query? Can this be done in one query? or I have to do this in sp?
Thanks lots.
January 23, 2009 at 1:28 pm
Sure it can be done in a query. Or a proc. Either one. Here's one way:
;with Breakdown as
(select division,
case
when score between 0 and 1000 then 1
else 0
end as Num_1_1000,
case
when score between 10001 and 2000 then 1
else 0
end as Num_1001_2000,
case
when score > 2000 then 1
else 0
end as Num_2000_plus
from dbo.MyTable)
select
Division,
sum(num_1_1000) as Num_1_1000,
sum(num_1001_2000) as Num_1001_2000,
sum(num_2000_plus) as Num_2000_plus
from
Breakdown
group by
Division;
There are others, but this one will generally get the job done. Though I think I didn't follow your actual names, but that's easy enough to fix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 1:38 pm
Thank you so much. It's amazingly working.
January 23, 2009 at 2:10 pm
Here is my final query:
;with Breakdown as
(select divisionname, avg(averagescore) as AverageScore,
case
when AverageScore between 0 and 1000 then 1
else 0
end as LowRisk,
case
when AverageScore between 1001 and 5000 then 1
else 0
end as MediumRisk,
case
when AverageScore > 5000 then 1
else 0
end as HighRisk
from applistwithscoreinfo
where AverageScore > 0 group by divisionname, averagescore)
select
divisionname,
sum(LowRisk) as LowRisk,
sum(MediumRisk) as MediumRisk,
sum(HighRisk) as HighRisk
from
Breakdown
group by
divisionname
Currently I got result like:
DivisionName, LowRisk, MediumRisk, HighRisk
What if I want to add AverageScore in the output?
Thanks again for the help.
January 23, 2009 at 2:18 pm
Please forget my last post, I already figured it out. Thanks anyway.
But this might not be the good one though.
select
b.divisionname, a.averagescore, a.numofapps,
sum(LowRisk) as LowRisk,
sum(MediumRisk) as MediumRisk,
sum(HighRisk) as HighRisk
from
Breakdown b
inner join
(select divisionname, avg(averagescore) as AverageScore,
count([appcode]) as NumOfApps
from applistwithscoreinfo
where AverageScore > 0
group by divisionname) a
on a.divisionname = b.divisionname
group by
b.divisionname,a.averagescore, a.numofapps
January 23, 2009 at 2:33 pm
Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply