SELECT with average value computation

[Ignatius88]

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?

[smantscheff]

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=?

[dipakatcvrca]

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 = ?;

[Ignatius88]

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.

[smantscheff]

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

[Ignatius88]

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.

[smantscheff]

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

[Ignatius88]

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$$

[smantscheff]

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.