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.

CodePudding user response：

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.

CodePudding user response：

A couple of things:

1. 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.
2. 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