November 4, 2011 at 8:35 am
When calculating PercentOnGradeLevel, I have to do 2 Casts and a Round to get the results with one decimal place. Is there an easier way of doing this than my mess?
select
SchoolID as [SchoolID_],
Grade_Level,
round(avg(Score),0) as [Median WPM Correct],
round(avg(Pts_Possible), 0) as [Pts Possible],
round(avg(Pct_Score), 0) as [Median Accuracy Rate],
count(*) as [Total Students],
(select count(*) from Benchmark where test_code like '%DIBELS%' and (Schoolid = b.SchoolID) and Alpha1='Y' and b.Grade_Level = Grade_Level) as [On Grade Level],
cast(round((cast((select count(*) from Benchmark where test_code like '%DIBELS%' and (Schoolid = b.SchoolID) and Alpha1='Y' and b.Grade_Level = Grade_Level) as numeric(10,2))/count(*))*100,1) as numeric (10,1))as [PercentOnGradeLevel],
(select count(*) from Benchmark where test_code like '%DIBELS%' and (Schoolid = b.SchoolID) and Alpha2='on' and b.Grade_Level = Grade_Level) as UAA
from
Benchmark b
where
test_code like '%DIBELS%' and (Schoolid = $$curschoolid$$ or $$curschoolid$$=0)
group by
SchoolID, Grade_Level
order by
SchoolID, Grade_Level
November 4, 2011 at 1:30 pm
-- just start out by multiplying by 1.0 then round() the result
declare @int1 int = 4
declare @int2 int = 5
select round(1.0*@int1/@int2,1)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 4, 2011 at 1:32 pm
-- if you want to specify that your output is numeric (10,1), you will have to cast or convert the result
declare @int1 int = 4
declare @int2 int = 5
select cast(round(1.0*@int1/@int2,1) as numeric(10,1))
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 7, 2011 at 6:41 am
Thanks
November 8, 2011 at 4:32 pm
Omit the ROUND function too. The cast to numeric will take care of the rounding.
November 8, 2011 at 5:09 pm
He's right. The round is redundundant in the second example. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply