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

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?

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?

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

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

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

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(Com

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

All Courses

From novice to tech pro — start learning today.

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)