30088121 30088121 - 2 months ago 8
MySQL Question

MYSQL Select reviewer name and SUM the reviews for them

I need to create a page that contains a list of reviewers, and for each reviewer the number of movies they have
reviewed.

I tried this, but it didnt give the desired output because i didnt know how to work the SUM into it.

SELECT review.revID, reviewer.name FROM review , reviewer WHERE review.revID = reviewer.revID


Any assistance would be apprectiated

here are my tables

CREATE TABLE reviewer (
revID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40),
email VARCHAR(40),
password VARCHAR(125)
);

CREATE TABLE movie (
movID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(30),
release_date date
);


CREATE TABLE review (
revID INT,
movID INT,
rating INT CHECK (rating > 0 AND rating < 10) ,
review_date datetime(6),
comment VARCHAR (300),
helpful INT,
PRIMARY KEY (revID,movID),

FOREIGN KEY (revID)
REFERENCES reviewer(revID),
FOREIGN KEY (movID)
REFERENCES movie(movID)
);

Answer

Try this query:

SELECT
count(movId) AS Total_Movie_Count,
r.name AS Reviewer_Name,
r.revId as Reviewer_Id
FROM review
INNER JOIN reviewer r on r.revId = review.revId
GROUP BY review.revId

You have to use GROUP BY for aggregated column movId. And to get the reviewer name, you have to INNER JOIN with revId.

Comments