asked on

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?

sammySeltzer

select avg(yourfield) from yourtable where yourfield <> 0

johnnyg123

ASKER

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

BrandonGalderisi

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

Christopher Gordon

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))

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyg123

ASKER

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)