# SQL select sum() ...ignoring null's

i have a table test1 (a int)
there are following values:
1
2
3
4
null

now i want select the sum of the column a, i do:
select sum(case when a is null then 0 end) from test1

should be 10 ..but it doesnt run how i specified. i want that the null is ignored. how do i do that?

also i want calculate the average of the table above. there are 4 times a value set, (1+2+3+4) / 4 = 2.5

thx a lot
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Your logic is missing a small item:

create table #test (a int null)

insert into #Test values (1)
insert into #Test values (2)
insert into #Test values (3)
insert into #Test values (4)
insert into #Test values (null)

select sum(case when a is null then 0 else a end) from #test

You gave the sum function a value only when a is null.
0
Commented:
you don't need the case

select sum(a.int),avg(a.int)

will ignore the null values  in the columns from the calculations automatically..
0
Commented:
SELECT avg(a),sum(a) from test1
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Pipped at the post again by lowfat ;)
0
Author Commented:
is there a way to find out the average, if the table has more columns than one?

table test1 (a int, b int, c int, d int)
a  b  c  d
2  4  9  null

==> average should be 5 .. how do i find that?
0
Commented:
select (isnull(a,0)+isnull(b,0)+isnull(c,0)+isnull(d,0)) /
( case when a is null 0 else 1 end+case when b is null 0 else 1 end+case when c is null 0 else 1 end+case when d is null 0 else 1 end)
0
Commented:
shogun's solution is the only way for sql server although i believe oracle has
function which work "on" a row..
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.