MySQL: averaging with nulls
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 table1where coalesce(col1, col2) is not null -- double nulls are eliminatedgroup by SomeArbitraryColhaving avg(coalesce(col1, 0) + coalesce(col2, 0)) < 500 and count(col3) > 3order by avg(coalesce(col1, 0) + coalesce(col2, 0)) asc;