# 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
``````
###### Who is Participating?

Billing EngineerCommented:
do this
``````AvgPoints = (cast( Sum(r.EntrantPoints) as decimal(20,4)) /Count(Distinct(r.EntrantID)))
``````
0

Author Commented:
That'll do perfect!  Thanks!
0

Chief 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
``````
0

Author Commented:
Any advantage between these two solutions?
0

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