CODEWITHSUNDEEP
sqlsql-servercaseaverage
Ariox66
Ariox66

Reputation: 660

Average and case in SQL Server 2012

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.

Upvotes: 6

Views: 1669

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

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

Upvotes: 7

Related Questions