Solved

# Rounding of result of calculation using aggregate functions

Posted on 2005-04-20
231 Views
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
Question by:cell-gfx

LVL 28

Accepted Solution

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

LVL 1

Author Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

#### 737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!