Average and value in SQL Server 2012

Question

I would like to have the average value of a column when it is greater than zero.

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

but I'm afraid the else clause is wrong. I want to ignore null values ​​on average.

Answer 1

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

Values

NULL will be fixed using Avg aggregate . This way you get an average value that is greater than 0 . Try it.

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

Demo

No 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 the else part in the 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