CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Average without calculate zero

I have some fields in the table, need to average those fields. Then I run this syntax, because I don't want to calculate 0 (zero) value.

SELECT myDate, AVG(CASE myField1 WHEN 0 THEN NULL ELSE myField1 END) AS avgmyField1 
FROM myTable WHERE myDate = '2014-06-01'

On my syntax, the average calculation means.. Make zero value to null. My question is, How if all values are zero...?

Thank you.

Upvotes: 1

Views: 607

Answers (3)

Steve Ford
Steve Ford

Reputation: 7753

You could also do this:

SELECT myDate, COALESCE(AVG(myField1), 0) AS avgmyField1 
FROM myTable WHERE myDate = '2014-06-01' and myField1 <> 0

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

Average will not use nullvalues to calculate an average value

IsNull or Coalesce can be used to change null values to different values.

This script will change 0 to null and take the average value:

SELECT IsNull(AVG(NullIf(val,0)), 0) 
FROM 
  (Values(5),(7),(0)) tbl(val)

Since 0 is excluded the result is 6

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Then you get NULL.

If you want zero instead, use COALESCE:

COALESCE( AVG(CASE myField1 WHEN 0 THEN NULL ELSE myField1 END) , 0)

Upvotes: 1

Related Questions