How to get the average of a column in MySQL

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.

Answer

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.