30088121 30088121 - 1 year ago 63
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download