How to ignore nulls in aggregate functions (AVG) when using multiple case statements

I am using SQL server to find the average of some columns, but I want to ignore any column that has a value of 0 or null

Table I am reading from:

My query:

SELECT AVG(CASE WHEN [January-Margin] > 0 THEN [January-Margin] ELSE NULL END + CASE WHEN [February-Margin] > 0 THEN [February-Margin] ELSE NULL END) as [Margin-Average] FROM MyTable

When using multiple case expressions inside the AVG() function, the column always returns NULL. What am I doing wrong?

UPDATE: As pointed out by @Gordon Linoff – this is an issue with the addition rather than the aggregate function. Can anyone suggest another way of achieving my desired result?

UPDATE: My desired result:

January-Margin  February-Margin March-Margin    AvgMargin
43.111          0.00            0.00            43.111
57.2897         0.00            0.00            57.2897
55.5352         56.8987         0.00            56.2169

Answer

Hmmm, I think you may need to substitute 0. The issue with the addition, not the average:

AVG(CASE WHEN [January-Margin] > 0 THEN [January-Margin] ELSE 0 END +
    CASE WHEN [February-Margin] > 0 THEN [February-Margin] ELSE 0 END
   ) as [Margin-Average]

If everything is 0, then use NULLIF() to ignore those rows:

AVG(NULLIF(CASE WHEN [January-Margin] > 0 THEN [January-Margin] ELSE 0 END +
           CASE WHEN [February-Margin] > 0 THEN [February-Margin] ELSE 0 END, 0
          )
   ) as [Margin-Average]

However, there are no such examples in your question.

EDIT:

Do you simply want this?

(AVG(NULLIF([January-Margin], 0) + AVG(NULLIF([February-Margin], 0))) / 2

This is kind of like the average of the values in the two columns where the values are not 0.

The actual average would be:

(SUM(NULLIF([January-Margin], 0) + SUM(NULLIF([February-Margin], 0))) / (COUNT(NULLIF([January-Margin], 0)) + COUNT(NULLIF([February-Margin], 0)))