Average and case in SQL Server 2012

Question

I'd like to have the average of a column when its bigger than zero.

Select Avg(Case when Column > 0 then Column else 0 end) as Avg

but I'm afraid the else clause is not correct. I want to ignore the zero values in the average.

Answer 1

Remove else part from case statement so the values less than 1 will be NULL.

Null values will be eliminated by the Avg aggregate. So you will get the average of values which are greater then 0. Try this.

Select Avg(Case when [Column]>0 then [Column] end) as [Avg]

DEMO

Without else part in case statement (Expected Average)

SELECT Avg(CASE WHEN a > 0 THEN a END) [Avg]
FROM   (SELECT 2 a UNION ALL SELECT 2 UNION ALL SELECT -1) bb

Result : 2

With else part in case statement.

SELECT Avg(CASE WHEN a > 0 THEN a ELSE 0 END) [Avg]
FROM   (SELECT 2 a UNION ALL SELECT 2 UNION ALL SELECT -1) bb 

Result : 1