using AVG() excluding cells with 0 in them

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 2005 Forums

Transact-SQL (2005)

using AVG() excluding cells with 0 in them

Author

Topic

Wozer
Starting Member

29 Posts

Posted - 2009-06-05 : 15:20:46

Does anyone know if there is a way to take the AVG() function so that it does not include any cells that have zeros in them. To give a better idea, If I have the following data being pulled by a query:


Expense 1 	      Expense 2
$1,000,000.00	$0.00
$80,000.00	$100,000.00
$10,000.00	$0.00
$5,000.00	$0.00
$7,000.00	$0.00
$20,000.00	$0.00
$10,000.00	$23,000.00

The average for Expense 1 should be $17,000. And Expense 2 should be $61,500.00 Not $17,571.42 Which is what I would get if I put in avg

I found something similar to the following code from Google that someone said would work, but it didn't change anything, though I might have it wrong:

AVG(CASE WHEN Expense2 > 0 Then Expense2 ELSE NULL END) AS "Expense2"

Thanks for your help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 15:39:11

AVG(NULLIF(Expense2,0)) should work because NULL values would not be taken by AVG().

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 15:48:56

Example
declare @sample table (val int)


insert @sample
select 100 union all
select 20 union all
select 0

select AVG(val) from @sample
select AVG(nullif(val,0)) from @sample

No, you're never too old to Yak'n'Roll if you're too young to die.

Wozer
Starting Member

29 Posts

Posted - 2009-06-05 : 16:04:18

That worked,

Thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 18:04:38

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources