Qry Returns an Integer on a calculation, but I want a decimal. How do I change that?

Friends,

I have the following qry:

Select Count(Distinct(r.EntrantID)) as NoOfEntrants,
Sum(r.EntrantPoints) as TeamTotalPoints,
AvgPoints = (Sum(r.EntrantPoints)/Count(Distinct(r.EntrantID)))
From Results r Inner Join Entrant e
ON r.EntrantID=e.EntrantID
AND
   r.SeasonID=14
AND
   r.TeamID=e.TeamID
Where r.TeamID=3 and e.IncludeInAvgReport='True'
And r.SeasonID=14 and r.EntrantPoints IS NOT NULL

and it returns the following values:

4      181      45

However, it should return 45.25.  How do I change my qry to return a decimal?

Best Regards,
Eric
Select Count(Distinct(r.EntrantID)) as NoOfEntrants,
Sum(r.EntrantPoints) as TeamTotalPoints,
AvgPoints = (Sum(r.EntrantPoints)/Count(Distinct(r.EntrantID)))
From Results r Inner Join Entrant e
ON r.EntrantID=e.EntrantID
AND
   r.SeasonID=14
AND
   r.TeamID=e.TeamID
Where r.TeamID=3 and e.IncludeInAvgReport='True'
And r.SeasonID=14 and r.EntrantPoints IS NOT NULL

Open in new window

indy500fanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do this
AvgPoints = (cast( Sum(r.EntrantPoints) as decimal(20,4)) /Count(Distinct(r.EntrantID)))

Open in new window

0
 
indy500fanAuthor Commented:
That'll do perfect!  Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
Angel is correct.  Another option is to do the following:
Select Count(Distinct(r.EntrantID)) as NoOfEntrants,
Sum(r.EntrantPoints) as TeamTotalPoints,
AvgPoints = (Sum(r.EntrantPoints) * 1.0/Count(Distinct(r.EntrantID)))
From Results r Inner Join Entrant e
ON r.EntrantID=e.EntrantID
AND
   r.SeasonID=14
AND
   r.TeamID=e.TeamID
Where r.TeamID=3 and e.IncludeInAvgReport='True'
And r.SeasonID=14 and r.EntrantPoints IS NOT NULL

Open in new window

0
 
indy500fanAuthor Commented:
Any advantage between these two solutions?
0
 
Kevin CrossChief Technology OfficerCommented:
When I have used them, performance has been the same for me.  Both fast.
My approach does implicit conversion to floating point if I am not mistaken, whereas other approach is more precise as to what you cast to if you need a specific output data type.  I tend to use my approach as it is shorter to type / adjust from original query, but as I said the other approach is correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.