MySQL: averaging with nulls

Question

Is there a simple way I can exclude nulls from affecting the avg? They appear to count as 0, which is not what I want. I simply don't want to take their average into account, yet here is the catch, I can't drop them from the result set, as that record has data on it that I do need.

Update:

example:

select avg(col1+col2), count(col3) from table1
where
group by SomeArbitraryCol
having avg(col1+col2) < 500 and count(col3) > 3
order by avgcol1+col2) asc;

This would be working for me, but the averages aren't accurate as they are counting null values as 0, which is really throwing off the whole average.

Answer 1

Aggregate functions (SUM, AVG, COUNT, etc) in SQL always automatically exclude NULL.

So SUM(col) / COUNT(col) = AVG(col) - this is great and consistent.

The special case of COUNT(*) counts every row.

If you make up an expression with NULLs: A + B where either A or B is NULL, then A + B will be NULL regardless of the other column being NULL.

When there are NULLs, in general, AVG(A + B) <> AVG(A) + AVG(B), and they will likely have different denominators, too. You would have to wrap the columns: AVG(COALESCE(A, 0) + COALESCE(B, 0)) to solve that, but perhaps also exclude the case where COALESCE(A, 0) + COALESCE(B, 0).

Based on your code, I would suggest:

select avg(coalesce(col1, 0) + coalesce(col2, 0)), count(col3) from table1
where coalesce(col1, col2) is not null -- double nulls are eliminated
group by SomeArbitraryCol
having avg(coalesce(col1, 0) + coalesce(col2, 0)) < 500 and count(col3) > 3
order by avg(coalesce(col1, 0) + coalesce(col2, 0)) asc;

Answer 2

AVG(number) 

Is the best way I can think of. This should automatically not include the nulls. Here is a little reading.

Answer 3

SELECT SUM(field) / COUNT(field)
FROM table
WHERE othercondition AND (field IS NOT NULL)

Link