locked
MySQL: Why was my answer wrong: Which film was shown in the Chaplin room most often in October 2017 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I just finished taking the MySQL course as a beginner and struggled a bit with the join and sub-queries statements. 

    For the question, Why is my answer incorrect?:

    **MY RESPONSE: **
    SELECT f.name, r.name, COUNT(s.room_id) AS film_times FROM films f
    JOIN screenings s ON f.id = s.film_id  
    JOIN rooms r ON s.room_id = r.id
    WHERE r.name = 'Chaplin';

    ****SOLUTION:****
    SELECT f.name, r.name, COUNT(r.name) AS film FROM films f
    JOIN screenings s ON f.id = s.film_id  
    JOIN rooms r ON s.room_id = r.id
    WHERE r.id = 1
    GROUP BY f.name
    ORDER BY film DESC
    LIMIT 1;
    Tuesday, May 26, 2020 12:31 AM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    First of all this  is SQL Server forum and not MySql, never mind, in your query you did not specify GROUP BY clause , it must be as you do an aggregation and have more columns in SELECT 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 26, 2020 4:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Data_2020,

    This is SQLServer Forum, please feel free to post your SQLServer issues here.

    However I have test your solution and there is the correct answer, that maybe my test data is simple. So could you please support some sample data?

    And this maybe helpful: mysql-group-by-and-sort-by-with-joins

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 26, 2020 8:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Data_2020,

    Is the reply helpful?

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 27, 2020 1:50 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Data_2020,

    Do you solve the issue?

    And is the reply helpful?

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 28, 2020 1:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    ****SOLUTION:****
    SELECT f.name, r.name, COUNT(r.name) AS film FROM films f
    ...
    GROUP BY f.name

    From ANSI SQL point-of-view the solution is also wrong. All not-aggregated columns must be listed in the GROUP BY clause; here is r.name missing, but MySQL implicit adds missing columns for query execution.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 28, 2020 6:08 AM
    Answerer