How to calculate in MySQL average value of data in a row?

mysql Leave comment

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);
-> 1
mysql> SELECT COALESCE(NULL,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.

  • Share/Bookmark

8 Responses to “How to calculate in MySQL average value of data in a row?”

  1. StefanNo Gravatar Says:

    Hi Michal,,

    you saved me a big headache. Thanks for providing this. Exactly what I was looking for. I would never have guessed that functionname. COALESCE?
    Well, thanks to you for showing how to use it.

  2. MMA Mail MagazineNo Gravatar Says:

    it would be very useful if you could show the tables and results of queries, just like what you get on the console.

  3. How to calculate in MySQL average value of data in a row? | Ligro Notepad Says:

    [...] found the article and tried to repeat the proposed method, but there is a problem. If a field not NULL, and such [...]

  4. BengtNo Gravatar Says:

    What happens if you have NULL values only in your table? Will there be a division by zero then? Anyway, thanx for the tip, will try to implement the code tomorrow.

  5. BillNo Gravatar Says:

    This is very useful as I have struggled with rows that contain NULL values. This will help to get accurate numbers.

  6. IcebergDelphiNo Gravatar Says:

    I had a table with string fields, when somebody edits the table, the fields null values converts to “” so what i did, i did the next (using your same example but with string fields):

    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=”",0, 1)
    COALESCE(V.rank_1=”",0, 1)
    COALESCE(V.rank_2=”",0, 1))
    ) AS row_avg FROM voting V

    If V.rank_X =”" Then V.rank_X new value is 0
    else V.rank_X new value is 1

    Thanks for your initial Idea.
    Greetings from Villaflores, Chiapas ,Mexico. HTMT NickName: IcebergDelphi

  7. touch screen car stereoNo Gravatar Says:

    This just saved me a lot of time on my project, thank you very much! Cheers!

  8. PrithviNo Gravatar Says:

    Heya..
    Hope you doing great.. Landed on your blog just now and found it pretty useful.. Gone through many posts. couldn comment on every of those page, so posting on this page. Keep up the good work.. :)

Leave a Reply

Verification Image

Please type the letters you see in the picture.

This site is using OpenAvatar based on

Comment Spam Protection by WP-SpamFree

Engine: Wordpress.
Log in