Comment

Michal Borychowski
How to calculate average of a row?,
February 17, 2009 at 2:01 AM

Another interesting usage of COALESCE() function is to calculate average value of data stored in one row - if we allow to store NULL values there.

When we have this table (three items per row):
CREATE TABLE IF NOT EXISTS `voting` (
`opinion_id` mediumint(9) unsigned NOT NULL auto_increment,
`rank_0` tinyint(4) default NULL,
`rank_1` tinyint(4) default NULL,
`rank_2` tinyint(4) default NULL,
PRIMARY KEY (`opinion_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `voting` VALUES (1, 1, 2, NULL);
INSERT INTO `voting` VALUES (2, 3, NULL, 4);
INSERT INTO `voting` VALUES (3, 3, 1, 2);

We can use this SELECT statement to get average values stored in a row:

SELECT *,
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V

I talk about it in more detail on my blog post at <a href="http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/" target="_blank">How to calculate average value of data in a row</a>