Average confusion - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Average confusion

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2006-05-09 : 11:36:39

I want to find the average of some numbers. If some of the numbers are zeros, then I donot want to include those numbers in the average (which can be done by using 'Where field_value <> 0). But if all the numbers are zeros, then I want the Average as 0. Is their a way to handle this?


1 5 0 3 -----> Avg =3
0 0 0 0 -----> Avg =0
4 5 4 3 -----> Avg =4

Thank you for your help in advance.
PKS

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-09 : 11:51:56

Here's one way...

--data
declare @t table (MyGroup int, b int)
insert @t
          select 1, 1
union all select 1, 5
union all select 1, 0
union all select 1, 3
union all select 2, 0
union all select 2, 0
union all select 2, 0
union all select 2, 0
union all select 3, 4
union all select 3, 5
union all select 3, 4
union all select 3, 3

--calculation
select MyGroup, isnull(avg(nullif(b, 0)), 0) as MyGroupAverage from @t group by MyGroup

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

sahu74
Posting Yak Master

100 Posts

Posted - 2006-05-09 : 12:36:17

Ryan,
That worked just perfect. Thanks a ton.

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources