How to get the average of a column in MySQL

Question

I'm trying to find a way to calculate the average of a column (as a double/float or decimal). I've found the AVG function in MySQL documentation but I can't get it to work. Currently I've got the following code:

public double GetRating(string username)
        {
            double average;

            MySqlConnection databaseConnection = new MySqlConnection(connectionString);
            string sumQuery = "select sum('rating') from " + username;
            string countQuery = "SELECT COUNT(*) FROM " + username;
            using (MySqlCommand sumCommand = new MySqlCommand(sumQuery, databaseConnection))
            using (MySqlCommand countCommand = new MySqlCommand(countQuery, databaseConnection))

            try
            {
                databaseConnection.Open();
                
                double sum = (Double)sumCommand.ExecuteScalar();
                double columnLength = (Double)countCommand.ExecuteScalar();
                average = sum / columnLength;
                return average;
             }

            finally
            {
                databaseConnection.Close();
            }

            return average;
        }

Now for some reason this does not work. It returns "Unable to cast object of type 'System.Int64' to type 'System.Double'."

The stored data in the database is an int but i'm trying to cast them to double. Any suggestions or solutions? Once again; double/float or decimal are usable for me.

you can use `AVG` function instead of calculating the sum and counting the rows and dividing the values. — Salman A, Dec 01 '21 at 12:36
I have tried using the AVG function, but I could not get it to work... Do you think you can provide me with an example? — Tom4045, Dec 01 '21 at 12:38
Wait: you have a different table for each username? Why would you do that? Also you can return multiple results from a single `SELECT` using `ExecuteReader` — Charlieface, Dec 01 '21 at 14:20

score 1 · Accepted Answer · answered Dec 01 '21 at 12:42

1

The built-in AVG function (an aggregate function) could be used like so:

select avg(rating) from table_name

Note that, like most aggregate functions, the average will exclude null values (the average of 1, 2, null is 1.5 instead of 1.0). Also, in MySQL the return datatype will be decimal if you're averaging decimal or integer columns so use the appropriate C# datatype.

answered Dec 01 '21 at 12:42

Salman A

262,204
82
430
521

Thank you very much, I didn't know it would return a decimal. Now it works – Tom4045 Dec 01 '21 at 12:46
1

Actually count returns an integer but sum returns integer, decimal or float depending on column. That was the problem in your original code. – Salman A Dec 01 '21 at 12:47

fiveelements · Answer 2 · 2021-12-01T13:13:00.100

1

A couple of things:

What is the data type of the rating column in the table? If it is an integer and not floating-point, please change the data type of sum accordingly to avoid the type-cast error.
As you already know, you may use avg() instead of sum() and then division by count(). Your SQL will look like:

select avg(rating) from table_name

edited Dec 01 '21 at 13:13

answered Dec 01 '21 at 12:43

fiveelements

3,649
1
17
16

(a) `avg('rating')` will calculate the average of a ***string literal*** which does not make sense. Corrected the code (b) unlike sql server, the average returns decimal for integer columns instead of integer so datatype does not need to be changed. – Salman A Dec 01 '21 at 13:03
The quotation was a typo and can be avoided. Modified. – fiveelements Dec 01 '21 at 13:12

How to get the average of a column in MySQL

2 Answers2