CODEWITHSUNDEEP
sqlmysql
Zombies
Zombies

Reputation: 25862

MySQL: averaging with nulls

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.

Upvotes: 10

Views: 22191

Answers (6)

FunkMasterFlex9000
FunkMasterFlex9000

Reputation: 1

I recently used this trick:

AVG(
    CASE
        WHEN
            valToBeAveraged IS NULL
        THEN
            0
        ELSE
            valToBeAveraged
    END
)

I believe the warning is just that. But this provides me with peace of mind because I know exactly how it works.

Happy coding

Upvotes: 0

TehShrike
TehShrike

Reputation: 10074

There's a good chance that you'll be able to get at the correct answer from what others have said here, but in case you have not:

Which values in your table MIGHT be NULL? And what do you want to happen if any of them are?

You never specify what results you want to see if col1 is NULL, or col2 is NULL, or if they both happen to be NULL, etc.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

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;

Upvotes: 19

Fry
Fry

Reputation: 4286

Answer based on original question:

SELECT AVG(t1.NumCol + t1.NumCol2), COUNT(table.NumCol) 
FROM 
(
SELECT NumCol, NumCol2 
FROM table 
WHERE (cond) AND (NumCol IS NOT NULL) AND (NumCol2 IS NOT NULL)
) t1,
table
WHERE (cond)

I think with the new restrictions, this still works in theory, but isn't the most efficient manner

Upvotes: 1

Matt Stephenson
Matt Stephenson

Reputation: 8610

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

Link

Upvotes: 3

Jacob Saylor
Jacob Saylor

Reputation: 2381

AVG(number)

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

Upvotes: 7

Related Questions