SELECT movie_id, movie_title, movie_release_year, movie_genre, movie_plot, (SELECT AVG(sum_movie_rate) FROM (SELECT SUM(movie_rate) AS sum_movie_rate FROM user_movie_rate WHERE movie_id = ?) AS t1) AS average_rating FROM movie WHERE movie_id = ?

when I retrieve a specific information of a movie, I want to calculate the average ratings of the movie.

user_movie_rate looks like this

CREATE TABLE `user_movie_rate` (
  `user_id` varchar(30) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `movie_rate` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`movie_id`),
  KEY `user_id` (`user_id`),
  KEY `movie_id` (`movie_id`),
  CONSTRAINT `movie_id` FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`fbID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

the query above does not return average value of the rating for a specific movie.

What is the problem?

You are selecting the sum instead of the average.
Try this instead:

SELECT m.movie_id, movie_title, movie_release_year, movie_genre, movie_plot, 
SUM(movie_rate) AS sum_movie_rate, 
AVG(movie_rate) as average_rating 
FROM movie m, user_movie_rate r 
WHERE m.movie_id = r.movie_id and m.movie_id=?
SELECT movie_id, movie_title, movie_release_year, movie_genre, movie_plot, (SELECT AVG(sum_movie_rate) FROM (SELECT SUM(movie_rate) AS sum_movie_rate FROM user_movie_rate WHERE movie_id = ?) AS t1) AS average_rating FROM movie WHERE movie_id = ?

when I retrieve a specific information of a movie, I want to calculate the average ratings of the movie.

user_movie_rate looks like this

CREATE TABLE `user_movie_rate` (
  `user_id` varchar(30) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `movie_rate` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`movie_id`),
  KEY `user_id` (`user_id`),
  KEY `movie_id` (`movie_id`),
  CONSTRAINT `movie_id` FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`fbID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

the query above does not return average value of the rating for a specific movie.

What is the problem?

You can try this also.

SELECT m.movie_id, m.movie_title, m.movie_release_year, m.movie_genre, m.movie_plot, AVG(mrt.movie_rate) AS Avg_movieRate 
    FROM user_movie_rate mrt 
INNER JOIN movie m 
    ON mrt.movie_id = m.movie_id
WHERE mrt.movie_id = ?;

You are selecting the sum instead of the average.
Try this instead:

SELECT m.movie_id, movie_title, movie_release_year, movie_genre, movie_plot, 
SUM(movie_rate) AS sum_movie_rate, 
AVG(movie_rate) as average_rating 
FROM movie m, user_movie_rate r 
WHERE m.movie_id = r.movie_id and m.movie_id=?

Doing that returns null values for everything except the id and the average ratings.

I tried to put m for all columns like m.movie_title, m.movie_release_year...

but no use.

Show a complete test case with CREATE TABLE statements, INSERT statements and the exact query which you are using.

Show a complete test case with CREATE TABLE statements, INSERT statements and the exact query which you are using.

CREATE TABLE `movie` (
  `movie_id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_title` varchar(250) NOT NULL,
  `movie_release_year` varchar(250) DEFAULT NULL,
  `movie_genre` text,
  `movie_plot` text,
  `average_rating` float DEFAULT '0',
  PRIMARY KEY (`movie_id`,`movie_title`)
) ENGINE=InnoDB AUTO_INCREMENT=662202 DEFAULT CHARSET=utf8$$

CREATE TABLE `user_movie_rate` (
  `user_id` varchar(30) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `movie_rate` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`movie_id`),
  KEY `user_id` (`user_id`),
  KEY `movie_id` (`movie_id`),
  CONSTRAINT `movie_id` FOREIGN KEY (`movie_id`) REFERENCES `movie` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`fbID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

This is create table and

pstmt = conn.prepareStatement("SELECT m.movie_id, m.movie_title, m.movie_release_year, m.movie_genre, m.movie_plot, SUM(movie_rate) AS sum_movie_rate, AVG(movie_rate) as average_rating FROM movie m, user_movie_rate r WHERE m.movie_id = r.movie_id AND m.movie_id = ?");

This is the query.

Where are the data (INSERT statements), where are your actual query parameters, where is the user table?

Where are the data (INSERT statements), where are your actual query parameters, where is the user table?

Is Insert statement necessary? what insert statement do you need?

CREATE TABLE `user` (
  `fbID` varchar(30) NOT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `nationality` varchar(50) DEFAULT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `genre` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`fbID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

If you don't provide a complete test case including test data (in the form of INSERT statements), I'm not able to further help you.