Q:

SQL C# How to get each piece of data with a certain attribute

Okay so I have a table of reviews each with a rating and a movieID pertaining to which movie is being rated. I add in multiple ratings to one movieID and want to get the average of the ratings. Right now I'm just attempting to compute the total of the ratings, but unfortunately I'm only coming up with the first entered rating from my database. I'm trying to get all the ratings store them into an ArrayList and compute the total, here is the code...
if (movieId > -1) { //if movie id was found
                sql = "select Rating from Reviews where MovieID = '" + movieId + "';";
                result.Close();
                dbCmd.Connection = dbConn;
                dbCmd.CommandText = sql;
                result = dbCmd.ExecuteReader();
                ArrayList total = new ArrayList();
                int i = 0;

                while (result.Read())
                {

                    try
                    {
                        total.Add((int)result.GetInt16(i));
                    }
                    catch (System.IndexOutOfRangeException)
                    {
                        MessageBox.Show("Looping, total count: " + total.Count);
                        break;
                    }
                    MessageBox.Show("Looping, total count: " + total.Count);
                    i++;
                }
                if (total.Count > 0) //if total was calculated
                {
                    //double avg = (double)result;
                    int computedTotal = 0;
                    foreach(int j in total)
                    {
                        computedTotal += j;
                    }
                        msg = "AVG Reviews successfully computed total result = " + computedTotal;

Any insight would be appreciated.

A:

As Liang hinted at, if you want the sum of ratings you can change your SQL query to:
SELECT SUM(rating) AS totalRating FROM reviews WHERE movieid = 'What you want here'

That way, you can just store an int that is returned and not have to worry about anything else. I would also like to add, that if you want to get the average, you can replace that with the AVG() aggregate function, like this:
SELECT AVG(rating) AS averageRating FROM reviews WHERE movieid = 'What you want here'

I would also like to add that I recommend you use parameterized queries instead of concatenating the string like that. I think you should change the first few lines to this:
dbCmd.Connection = dbConn;
dbCmd.CommandText = "SELECT AVG(rating) AS averageRating FROM reviews WHERE movieID = @movieid";
dbCmd.CommandText.Parameters.AddWithValue("@movieid", movieId");

Here is info on aggregate functions and parameterized queries.

