When programming some voting system I wanted to count in MySQL average of values inserted in one row. If I had data in a column and wanted to return average I would have used the AVG() function but I wanted to return average from a row.
You can say – where is the problem? Let’s sum everything together and divide by the number of items.
Yes, but I wanted that in my voting system you don’t have to rank all the elements, e.g. let’s presume we have: cuisine, music and appearance. If someone wants to give a rank for music it is not obligatory to rank cuisine and appearance.
That’s why I had to use NULL values in my table. I came up with this structure:
DROP TABLE IF EXISTS `voting`; 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 COLLATE=latin1_general_ci;
And let’s insert these values:
INSERT INTO `voting` VALUES (1, 1, 2, NULL); INSERT INTO `voting` VALUES (2, 3, NULL, 4); INSERT INTO `voting` VALUES (3, 3, 1, 2);
When we do:
SELECT *, (V.rank_0 + V.rank_1 + V.rank_2) / 3 AS row_avg FROM voting V
we only receive correct averages for the rows where all values are not NULL. But when I have e.g. 3, NULL, 4 I’d like to get 3.5 as a return. That’s the moment when function COALESCE() comes handy.
What does COALESCE () do? From MySQL manual we have:
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
mysql> SELECT COALESCE(NULL,1);
mysql> SELECT COALESCE(NULL,NULL,NULL);
And these information will help us to build another SELECT statement:
SELECT *, #first part (COALESCE(V.rank_0, 0) + COALESCE(V.rank_1, 0) + COALESCE(V.rank_2, 0)) / #second part (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
And now we have the average returned just as we wanted. NULLs are not taken into account when we do the calculations.
So what is inside this SELECT statement? COALESCE() returns the first non-NULL value so in the first part COALESCE(V.rank_0, 0) would return 0 if rank_0 is NULL or rank_0 itself if it is not NULL. The whole first part of SELECT would give us the sum of non-NULL values in the row.
In the second part we have (COALESCE(V.rank_0 – V.rank_0, 1) which would return 1 only if rank_0 is NULL (rank_0 – rank_0 is still NULL). We would receive 0 if rank_0 is not NULL (e.g. 4 – 4 = 0 and it is first non-NULL value in the list to be returned). So the second part let’s us count the number of columns which have NULL values and subtract it from the number of all items.
This way we have our average from row in MySQL database :)
Do you know any other useful things we can do with COALESCE function or averages in general? Please share them with us.