CODEWITHSUNDEEP
mysqlsqlsql-update
RGriffiths
RGriffiths

Reputation: 5970

Query calculating average of values including NULL

I have a query as follows:

UPDATE table SET result = ROUND((col1 + col2)/2, 2);

This is fine if there is a value in both col1 and col2 but if one of them is null then it returns null. How do I resolve this issue?

Upvotes: 0

Views: 26

Answers (1)

GMB
GMB

Reputation: 222432

You could do:

UPDATE table 
SET result = ROUND(
    (coalesce(col1, 0) + coalesce(col2, 0)
    / nullif( (col1 is not null) + (col2 is not null), 0), 
2);

The numerator adds both columns, while turning null values to 0. The denominator counts how many values are not null. In other words, if there are two non-null values, you get their average, while if there is just one you get the value itself.

If both values are null, the result is still null (which seems like the relevant thing to do here).

Upvotes: 1

Related Questions