AVG Aggregate Function. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

AVG Aggregate Function.

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-08-31 : 06:52:41

Hi All,
I have a table it's description is:

create table #msora
(
id float null
,name varchar(30)
)

The data in the table is:
id name
-------------------------------------
1.0 ms one
2.0 ms two
3.0 ms three
4.0 ms four
3.0 ms Duplicate three
NULL ms NULL test

Now i queried for average of an column ID
select avg(id) as average from #msora
The output is:
average
------------------
2.6000000000000001

The sum is 13.0 the count is 5 then avg become 2.6
but why i am getting 2.6000000000000001

Thanks.

M.MURALI kRISHNA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-31 : 07:00:13

This is because FLOAT is an approximate representation http://msdn.microsoft.com/en-us/library/ms173773.aspx

You can round the results to the appropriate number of decimal digits you like:

select ROUND(avg(id),1) as average from #msora;
-- or
select CAST(avg(id) AS DECIMAL(19,2)) as average from #msora

Decimal data type represents numbers exactly.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-31 : 07:01:37

Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

http://msdn.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

Too old to Rock'n'Roll too young to die.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-08-31 : 07:12:52

Thank's alot sunitabeck & webfred.

M.MURALI kRISHNA

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-08-31 : 07:35:15

Hi All,
one more clarification plz,why subqueries and aggregate functions are not allowed in aggregate functions?

My Assumption related to nested aggregate functions is:
1.avg(sum(ID)) inner function always return an single scalar value,Hence outer aggregate function for a single scalar valus is meaningless..
is my assumption correct?

2.And what about sub queries,sub queries may return single or multiple values?

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:28:08

quote:
Originally posted by mmkrishna1919

Hi All,
one more clarification plz,why subqueries and aggregate functions are not allowed in aggregate functions?

My Assumption related to nested aggregate functions is:
1.avg(sum(ID)) inner function always return an single scalar value,Hence outer aggregate function for a single scalar valus is meaningless..
is my assumption correct?

2.And what about sub queries,sub queries may return single or multiple values?

M.MURALI kRISHNA

you cant nest aggregation functions in t-sql
for that you need to apply them separately. ie form a derived table for the first calculation and then apply second one over it

in your ex.


SELECT AVG(Total)
FROM
(
SELECT SUM(ID) AS Total
FROM table
....
)t

i hope the above code was for illustration as sum over id makes no sense in actual

subqueries may return single values or multiple values depend on where you use them

if you're using in select list it should return only one value

if in where using exists,in conditions it can return multiple

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources