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?
johnnyg123Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Christopher GordonConnect With a Mentor Senior 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
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.