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?