Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL AVG function does not return decimal, only unrounded integers

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
metalaureate
Asked:
metalaureate
  • 2
2 Solutions
 
Dishan FernandoSoftware Engineer / DBACommented:
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
 
somchaiuCommented:
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
 
Dishan FernandoSoftware Engineer / DBACommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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