IS NOT NULL - adding potentially null value columns together

Here is the situation:

You have 3 columns that need to be averaged, but...they can each be null. To make matters worse, you have no idea HOW MANY will be null!

-- Table structure for table `ratings`

CREATE TABLE IF NOT EXISTS `ratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`condition_1` tinyint(1) DEFAULT NULL,
`condition_2` tinyint(1) DEFAULT NULL,
`condition_3` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

INSERT INTO `ratings` (`id`, `condition_1`, `condition_2`, `condition_3`)
VALUES (1, 2, 3, 4), (2, 1, 5, NULL);

Your problems are twofold. First, if you add NULL to anything in Mysql, the answer is NULL.

Check for yourself:

SELECT `condition_1` + `condition_2` + `condition_3`
FROM `ratings`

so adding them and dividing by 3 won't work.

IFNULL will come to our rescue on this one:

SELECT IFNULL(`condition_1`, 0) + IFNULL(`condition_2`, 0) + IFNULL(`condition_3`, 0)
FROM `ratings`

Simply change nulls to zeros for addition.

The second issue is our divisor. We don't know how many of these fields will be null for each row; we can't divide by 3 each time, since we may only have one or two good values, and it will skew our results. What we want is something that will count each non-null column as 1, and each null will be ignored.

Something like IS NOT NULL.

We all know "IF (x is not null)... " logic. We'll just leverage that a little differently than we are used to:

SELECT (`condition_1` IS NOT NULL) + (`condition_2` IS NOT NULL) 
+ (`condition_3` IS NOT NULL) FROM `ratings`

This will evaluate each column to be either 1 or 0, so we can add the non-nulls.

Now we have:

SELECT (IFNULL(`condition_1`, 0) + IFNULL(`condition_2`, 0) + IFNULL(`condition_3`, 0) )
/ ((`condition_1` IS NOT NULL) + (`condition_2` IS NOT NULL) + (`condition_3` IS NOT NULL))
FROM `ratings`

Hooray!

But wait...What if NONE of the columns have a value? Thought the words "Divide by zero error" were banished from your life forever?

One more little gimmick will do just that:

SELECT (IFNULL(`condition_1`, 0) + IFNULL(`condition_2`, 0) + IFNULL(`condition_3`, 0) )
/ 
GREATEST(
 (`condition_1` IS NOT NULL) + (`condition_2` IS NOT NULL) + (`condition_3` IS NOT NULL), 1
)
FROM `ratings`

Use GREATEST to take either the divisor or 1, so your math is ok.

There you go. Hope that was helpful.

Contact me