Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 270

# Rounding of result of calculation using aggregate functions

I am performing the following calculation in my query :

select sum(a.daysinarrears)/sum(Case When a.daysinarrears > 0 Then 1 Else 0 end) as 'Days In Arrears Average',
sum(a.days) as 'Total Days In Arrears',
sum(Case When a.days > 0 Then 1 Else 0 end) as 'Count Of Contracts In Arrears'
from tablea a
where (month(a.date) = 3 and year(a.date) = 2005)

In my example, I am trying to get the average number of days in arrears from tablea but only where tablea.days is greater than zero.
If I run the query, I get the following :
Total Days In Arrears : 424
Count of Contracts In Arrears : 325
Days In Arrears Average : 1

I expect to see the Days In Arrears Average to show as 1.304... but it always gets rounded to 1.

Please can someone tell me what I'm doing wrong?

Cheers!
0
cell-gfx
1 Solution

Commented:
Try casting it to decimal:

select cast(sum(a.daysinarrears) as decimal(12, 3))/cast(sum(Case When a.daysinarrears > 0 Then 1 Else 0 end) as decimal(12,3)) as 'Days In Arrears Average',
sum(a.days) as 'Total Days In Arrears',
sum(Case When a.days > 0 Then 1 Else 0 end) as 'Count Of Contracts In Arrears'
from tablea a
where (month(a.date) = 3 and year(a.date) = 2005)

That should to the trick.
0

Author Commented:
rafrancisco,

Oddly enough, I cast each side as floats just after I submitted the question, solving my own problem!
But, seeing as how this is a useful bit of knowledge to have out there, I'll still give you the points!

cell
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.