Comment
Michal Borychowski
How to calculate average of a row?,
February 17, 2009 at 2:01 AM
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>
View All Comments
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>
View All Comments
Related Tutorial and Articles
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
