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.
| Author |
Topic |
|
o9z
Starting Member
23 Posts |
 Posted - 2008-04-08 : 12:35:52
|
| I am selecting the following fieldsAVG_BackAVG_YieldI want to select both fields, like thisSelect AVG(AVG_Back), AVG(AVG_Yield)FROM tblUserWHERE 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
52326 Posts |
Posted - 2008-04-08 : 12:38:34
|
| [code]Select AVG(CASE WHEN AVG_Back>0 THEN AVG_Back ELSE NULL END), AVG(AVG_Yield)FROM tblUserWHERE Date Between '3/1/2008' AND '3/31/2008'[/code] |
 |
|
|
o9z
Starting Member
23 Posts |
Posted - 2008-04-08 : 12:50:23
|
| hmm, that syntax is not working for me. |
|
|
|
o9z
Starting Member
23 Posts |
Posted - 2008-04-08 : 12:51:35
|
| nm, it worked. I had an extra character |
|
|
|
mabeale
Starting Member
1 Post |
Posted - 2012-08-02 : 11:55:10
|
A nicer way would be to do the following:AVG(NULLIF(AVG_Back,0)) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-02 : 12:47:47
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
How to exclude data from an average?