0

I have 3 tables like this. For my homework I have to make a query which is Select a Dvd name, dvd category and average of rates for criticized by at least two customer

     CREATE TABLE dvd(
    `dvdId` INT NOT NULL,
    `Name` VARCHAR(45) NULL,
    `Category` VARCHAR(45) NULL,
   `Price` INT NOT NULL,
    PRIMARY KEY (`Id`));  

     CREATE TABLE Subtitles(
    `dvdId` INT NOT NULL,
    `Language` VARCHAR(45) NULL);
     
   CREATE TABLE critic(
    `dvdId` INT NOT NULL,
   `customerName` VARCHAR(45) NULL,
   `rate` INT NOT NULL,
   `comment` VARCHAR(45) NULL);

I'm trying to do this but this query doesn't work as I wish. Can anyone help me?

SELECT name,catergory, avg(rate) as rate FROM Dvd INNER JOIN Critic ON 
Dvd.dvdId=Critic.DvdId
GROUP BY customerName
HAVING COUNT(*) >2;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
marango1
  • 29
  • 3
  • 1
    There is no 'catergory' here – Strawberry Jun 26 '20 at 05:44
  • HAVING COUNT(*) >1; – Krishan Kumar Jun 26 '20 at 05:53
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Jun 26 '20 at 19:50
  • This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. – philipxy Jun 26 '20 at 19:50
  • Duplicate of [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) – philipxy Jun 26 '20 at 19:56

3 Answers3

0

Try this below script-

The basic of GROUP BY is, that you have to GROUP BY with all non aggregated columns you selected in the SELECT part of your query. In your query, you have selected name and catergory column as non aggregated column, but did not added them in the GROUP BY properly.

SELECT name,Category, -- you have wrong column name "Category" in your query
avg(rate) as rate 
FROM Dvd 
INNER JOIN Critic 
ON Dvd.dvdId=Critic.DvdId
GROUP BY name,Category
-- Add all non aggregated columns in the GROUP BY from the SELECT part
HAVING COUNT(*) >2;

Note: You can also add column "customerName" in both SELECTION and GROUP BY part if the value is required. But just keep in mind, adding a new column in GROUP BY means creating new dimensions/groups in the output. So you have be careful of adding columns in GROUP BY.

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
0

You should add all non-aggregated fields in group by. Here is really good read about why the selected column have to be in the group by clause .

Always try to alias your tables and try the following.

SELECT 
  name,
  catergory, 
  avg(rate) as rate 
FROM Dvd d
JOIN Critic c
ON d.dvdId = c.DvdId
GROUP BY 
  name,
  catergory
HAVING COUNT(*) > 2
zealous
  • 7,336
  • 4
  • 16
  • 36
0

Change your sql as below:-

SELECT name,catergory, customerName, avg(rate) as rate 
FROM Critic left join Dvd  ON Dvd.dvdId=Critic.DvdId
GROUP BY customerName
HAVING COUNT(customerName) >1;

Let me know if it solves your issues.

Krishan Kumar
  • 394
  • 4
  • 13