My java program is a movie rating system that is connected to a MySQL movies database. With my program, I need to be able to get the average rating score for a specific movie title. My program is creating a running sum of all the rating for a designated movie. I'm not sure where I'm going wrong. Any help would be appreicated.

My java code for the rating section of the program is below:

//Rate movie

//add rating: add to running sum of rating when user inputs rating

//use count to divide by sum to get total average

System.out.println("Enter movie you want to rate: ");

movie_title = input.nextLine();

System.out.println("Enter your rating (1-10): ");

score = input.nextDouble();

int numRatings = 0;

try {

numRatings++;

//Search movie

String sqlStatement = "SELECT * FROM movie WHERE movie_title like\"%" + movie_title+"%\"";

PreparedStatement pstmt = connection.prepareStatement(sqlStatement);

ResultSet resultSet = pstmt.executeQuery();

while(resultSet.next()) {

String data = resultSet.getString("movieID") +", " + resultSet.getString("movie_title") +

", " + resultSet.getDouble("score") + ", " + resultSet.getDouble("sumScore") + ", "

+ resultSet.getDouble("countMovie");

sumScore = resultSet.getDouble("sumScore");

sumScore = sumScore + score;

countMovie = resultSet.getDouble("countMovie");

avgScore = (sumScore)/numRatings;

//numRatings ++;

}//end of while loop

}catch(SQLException ex) {

System.out.println("Not selected. Error: " + ex);

}

catch(Exception e) {

System.out.println("Error: " + e);

}

//Add movie rating

try {

PreparedStatement pstmt = connection.prepareStatement("update movie set score = ?, sumScore = ?, avgScore = ? where movie_title like\"%" + movie_title + "%\"");

pstmt.setDouble(1, score);

pstmt.setDouble(2, sumScore);

pstmt.setDouble(3, avgScore);

pstmt.executeUpdate();

//confirm rating was added

System.out.println("Sucessfully Added!");

}catch(SQLException ex) {

System.out.println("Error: " + ex);

}

catch(Exception e) {

System.out.println("Error: " + e);

}

break; //break out of case 2

My SQL Script is below:

CREATE DATABASE moviesdatabase; USE moviesdatabase;

CREATE TABLE MOVIE (movieID CHAR(15), movie_title VARCHAR(45) NOT NULL, score decimal(4,2), sumScore decimal(4,2), avgScore decimal(4,2), countMovie int NOT NULL auto_increment Primary key);

CREATE TABLE COMMENTS (movieID CHAR(15), comments CHAR(65), commentID CHAR(15) PRIMARY KEY);

CREATE TABLE RATING (ratingID CHAR(15) NOT NULL, rating decimal(3,2) NOT NULL, movieID CHAR(15) Primary key #foreign key );