Solved

SQL AVG function does not return decimal, only unrounded integers

Posted on 2004-04-19
3
5,327 Views
Last Modified: 2012-06-27
Dear Experts,

I am sure this is a pretty stupid question. Anyway, the following query

Select AVG(FIELDNAME) As AVGFIELDNAME ... only returns integers. What do I do to return  a decimal?

Thanks.
0
Comment
Question by:metalaureate
  • 2
3 Comments
 
LVL 8

Expert Comment

by:dishanf
Comment Utility
create table #t
(
t decimal(5,3)
)

insert into #t values(3.4)
insert into #t values(4.5)

select * from #t

select avg(t) from #t
----------------------------------
3.950000

(1 row(s) affected)
0
 
LVL 2

Accepted Solution

by:
somchaiu earned 250 total points
Comment Utility
The problem of AVG() function is if the input is "int" the result value is "int" too. You have to convert input to decimal to receive decimal result. for example:

1. SELECT AVG(FIELDNAME+0.0) AS AVGFIELDNAME

2. SELECT AVG(CAST(FIELDNAME AS decimal)) AS AVGFIELDNAME
0
 
LVL 8

Assisted Solution

by:dishanf
dishanf earned 250 total points
Comment Utility
Yes .. if your column is int you must cast or convert to decimal

select avg(CAST(ColumnName AS decimal(5,2))) from TableName
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now