Performing aggregate calculations on null values

Question

Post reply

Performing aggregate calculations on null values

krzychek

Grasshopper

Points: 24
More actions
September 10, 2003 at 9:12 am

#81108

This should be an easy one for the pros in here. Bear with me, I'm still learning the ropes:
If I have a field called "AGE" which allows for nulls, and I attempt to do an average on this column, how are the null values handled? Are they factored into the average as zeros, or just ignored?
Thanks for the help!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

swp Valued Member Points: 67 More actions · Answer 1

swp

Valued Member

Points: 67

September 10, 2003 at 9:31 am

#473461

you can try

avg(isnull(age,0))

krzychek Grasshopper Points: 24 More actions · Answer 2

Well I don't want to assign a value of zero to it, I just want to ignore it if it is null. Does AVG does that by default?

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 3

The AVG function will ignore NULL values in its calculation and provide a warning that NULL value is eliminated. You could test it easily.

krzychek Grasshopper Points: 24 More actions · Answer 4

krzychek

Grasshopper

Points: 24

September 10, 2003 at 11:05 am

#473505

That's all I needed to know! Thanks!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
The AVG function will ignore NULL values in its calculation and provide a warning that NULL value is eliminated. You could test it easily.

that's right.

In addition, you know, that taking your average this way might more or less vary from the 'real' average. I mean one pitfall might be something like the following pseudoSQL code

AVG(age) / COUNT (All_Data).

To be (more) precise you should use

AVG(age) / COUNT (age is not null)

Just my $0.02 cents anyway

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Stupid, stupid Frank

Forget my last post.

AVG already does this for you!

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rogerdmoran Newbie Points: 1 More actions · Answer 7

But look at the following:

CREATE TABLE #UserNameAge (

UserName varchar (50) NOT NULL ,

Age int NULL

) ON [PRIMARY]

INSERT INTO #UserNameAge(UserName, Age) VALUES ('Andy', 30)

INSERT INTO #UserNameAge(UserName, Age) VALUES ('Brad', NULL)

INSERT INTO #UserNameAge(UserName, Age) VALUES ('Chet', 30)

SELECT AVG(Age) FROM #UserNameAge-- result = 30

SELECT SUM(Age)/COUNT(Age) FROM #UserNameAge-- result = 30

SELECT SUM(Age)/COUNT(*) FROM #UserNameAge-- result = 20

DROP TABLE #UserNameAge

Domain aggregate functions (SUM, AVG, COUNT etc) ignore nulls except for COUNT which will include nulls if you use the COUNT(*) syntax instead of COUNT(<column>) syntax.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

quote:
Domain aggregate functions (SUM, AVG, COUNT etc) ignore nulls except for COUNT which will include nulls if you use the COUNT(*) syntax instead of COUNT(<column>) syntax.

yes, I already crucified myself

Frank

Wenn Englisch zu schwierig ist?

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]