Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

ms sql server 2005 non zero avg query

I have the following table named Complaints

shop  complaint1  complaint2  complaint3
1200  0                 5                 10


I am trying to write a query that will avg the non zero fields


Using the above example I would like 7.5 to be returned

is there an easy way to do this?
0
johnnyg123
Asked:
johnnyg123
1 Solution
 
sammySeltzerCommented:
select avg(yourfield) from yourtable where yourfield <> 0
0
 
johnnyg123Author Commented:
I guess my example wasn't the greatest.  

I'm not looking to avg a single field

there are 3 different fields  (complaint1, complaint2 and complaint3) which I want to add up and get the everage of (but only if it is non zero)  If all  3 fields are 0 I want the average to be 0

if complaint1 is 2,   but complaint2 is 0 and complaint3 is 0


I am looking to get an average of 2
0
 
BrandonGalderisiCommented:
you can't avg() across multiple fields like that.  You have to sum and divide by the number of non-zero values.

example:

CREATE TABLE #test (a INT,b INT,c INT)
INSERT INTO #test( a, b, c )
VALUES ( 0,5,10)
go
SELECT SUM(a+b+c) / (SUM(CASE WHEN a=0 THEN 0 ELSE 1 END + CASE WHEN b=0 THEN 0 ELSE 1 END + CASE WHEN c=0 THEN 0 ELSE 1 end)*1.0)
FROM #test t
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Christopher GordonSenior Developer AnalystCommented:
select

cast((isnull(column1,0) + isnull(column2,0) + isnull(column3,0)) as decimal(10,2)) /
((case when isnull(column1,0) > 0 then 1 else 0 end) + (case when isnull(column2,0) > 0 then 1 else 0 end) + (case when isnull(column3,0) > 0 then 1 else 0 end))
0
 
Christopher GordonSenior Developer AnalystCommented:
Another thought on my post.  If all of the columns are 0, you'll run into a divide by 0 error.  You'll need to wrap the divisor with a  NULLIF() function, which will turn the 0 to null thus preventing the error.

select

cast((isnull(column1,0) + isnull(column2,0) + isnull(column3,0)) as decimal(10,2)) /
nullif(((case when isnull(column1,0) > 0 then 1 else 0 end) + (case when isnull(column2,0) > 0 then 1 else 0 end) + (case when isnull(column3,0) > 0 then 1 else 0 end)),0)
0
 
johnnyg123Author Commented:
Thaniks

I wrapped an isnull around the whole statement so that if all 3 values were null that it would return 0 instead of null

    ISNULL(CAST(ISNULL(SUM(ComplaintM1), 0) + ISNULL(SUM(Complaintm2), 0) + ISNULL(SUM(Complaintm3), 0) AS decimal(10, 2))
                      / NULLIF ((CASE WHEN isnull(SUM(ComplaintM1), 0) > 0 THEN 1 ELSE 0 END) + (CASE WHEN isnull(SUM(ComplaintM2), 0) > 0 THEN 1 ELSE 0 END)
                      + (CASE WHEN isnull(SUM(ComplaintM3), 0) > 0 THEN 1 ELSE 0 END), 0), 0)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now