Learn how to a build a cloud-first strategyRegister Now

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

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
Asked:
cell-gfx
1 Solution
 
rafranciscoCommented:
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
 
cell-gfxAuthor 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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