Rounding to one decimal place.

• Opus

  Default port

  Points: 1419

  More actions

  November 4, 2011 at 8:35 am

  #390766

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 4, 2011 at 1:30 pm

  #1403480

  -- 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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 4, 2011 at 1:32 pm

  #1403481

  -- 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

• Opus

  Default port

  Points: 1419

  More actions

  November 7, 2011 at 6:41 am

  #1403918

  Thanks

• nogoodboyo

  SSC-Addicted

  Points: 418

  More actions

  November 8, 2011 at 4:32 pm

  #1404910

  Omit the ROUND function too. The cast to numeric will take care of the rounding.

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 8, 2011 at 5:09 pm

  #1404921

  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