avg() - 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()

Author

Topic

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-25 : 04:49:54

If we have a column, X, with values 1, 2, 3, and NULL, what is AVG(X)?

actually iam following one book but there are no answers
well for this i think answer is null becoz we consider null as unknown
so(6+unknown)/4 must be unknown which is null

is it correct?some one tell me

thanks
XX

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-25 : 04:58:07

[code]declare @table table (
number int
)
insert into @table
select 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT NULL

select AVG(number) from @table[/code]The NULL columns are disregarded so it will be (6 / 3) = 2

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 04:59:36

Have you tried testing it?

declare @t table (i int)
insert @t select 1
insert @t select 2
insert @t select 3
select avg(i) from @t
2
insert @t select null
select avg(i) from @t
2

Actually it is the sum of the values divided by the number of non null values.
Remember that it will be an integer calculation for integers so if you change that last null to 0 the result will be 1.
You can change to avg(i*1.0) to get a float calculation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 05:02:45

Atually if you look at bol under remarks it says

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value an error will be returned.

and it gives the result datatypes depending on input.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources