Using AVG aggregate function with decimal

elygp · May 7th, 2007, 01:03 AM

Hi,

I am using AVG function in one of my Views to create summary of records. The View works fine except that the average displays whole numbers only. I even tried using ROUND function, but still the AVG function gets whole numbers only. Anyone who has idea on this? Thanks so much!

SELECT ProjectID, BatchCode, WeekNo,
ROUND(AVG completionratetotal), 2) AS WeeklyCompletionAverage
FROM dbo.CompletionRateTotalNoNegativeView
GROUP BY ProjectID, BatchCode, WeekNo

happygv · May 7th, 2007, 01:07 AM

What is the datatype of "completionratetotal" column? If that is already a decimal type, you dont have to manipulate it to get decimals. ROUND function is used to round the value to specific decimals points.

I doubt the completionratetotal is an INT type defined in the table. In this case you need to typecast the completionratetotal's value to decimal by using

Code:

CAST(AVG(completionratetotal) as DECIMAL(10,2))

Hope that helps.

cheers.

_________________________
- Vijay G
Strive for Perfection

elygp · May 7th, 2007, 09:04 PM

Hi ViJay,
Thanks for the help! It works! :)

More power to you!