SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to exclude data from an average?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

o9z
Starting Member

23 Posts
Posted - 04/08/2008 :  12:35:52  Show Profile  Reply with Quote
I am selecting the following fields

AVG_Back
AVG_Yield

I want to select both fields, like this

Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'

I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 04/08/2008 :  12:38:34  Show Profile  Reply with Quote 
Select AVG(CASE WHEN AVG_Back>0 THEN AVG_Back ELSE NULL END), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'
Go to Top of Page

o9z
Starting Member

23 Posts
Posted - 04/08/2008 :  12:50:23  Show Profile  Reply with Quote
hmm, that syntax is not working for me.
Go to Top of Page

o9z
Starting Member

23 Posts
Posted - 04/08/2008 :  12:51:35  Show Profile  Reply with Quote
nm, it worked. I had an extra character
Go to Top of Page

mabeale
Starting Member

1 Posts
Posted - 08/02/2012 :  11:55:10  Show Profile  Reply with Quote
A nicer way would be to do the following:

AVG(NULLIF(AVG_Back,0))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 08/02/2012 :  12:47:47  Show Profile  Reply with Quote
quote:
Originally posted by mabeale

A nicer way would be to do the following:

AVG(NULLIF(AVG_Back,0))


what if it has negative values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000