Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

Telmo Teixeira
Telmo Teixeira
16,378 Points
Posted by Telmo Teixeira
Telmo Teixeira
16,378 Points

Error Code: 1055 "...sql_mode=only_full_group_by"

As i follow along the exercise in the video, i got this error when executing this command:

SELECT title, MIN(score) AS minimum_score, MAX(score) AS maximun_score, AVG(score) AS average_score FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id;

in mysql documentation i've found that above MySQL 5.7.5 the sql_mode=only_full_group_by is set by default.

The solution that i found to make it work, was to remove this option from the sql_mode.

I've used "SELECT @@sql_mode", to view my current set options, and then reset them without the "ONLY_FULL_GROUP_BY".

"SET sql_mode='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';"

chrisverra
chrisverra
3,760 Points

when you use 'AS' you have to give it a name first before continueing to call other columns

i.e. MIN(score) AS MinimumScore, MAX(score).......

4 Answers

Jose yanez
Jose yanez
10,042 Points
Jose yanez
Jose yanez
10,042 Points

If you add movies.id at the end, the query works. I don't know why but I found this solution by guessing.

SELECT title, MIN(score) AS minimum_score, 
MAX(score) AS maximum_score, 
IFNULL(AVG(score),0) AS average
FROM movies LEFT OUTER JOIN reviews
ON movies.id = reviews.movie_id
 GROUP BY movie_id, movies.id;
Telmo Teixeira
Telmo Teixeira
16,378 Points
Telmo Teixeira
Telmo Teixeira
16,378 Points

Hi Chris,

thanks for the input, but i don't think i've missed any name after the 'AS'.

James Ma
James Ma
19,063 Points
James Ma
James Ma
19,063 Points

I seem to be getting the same error too.. everything looks correct.

SELECT title, min(score) AS min_score,
    max(score) AS max_score, 
    avg(score) AS average 
FROM movies LEFT OUTER JOIN reviews
 ON movies.id = reviews.movie_id
 GROUP BY movie_id;
James Ma
James Ma
19,063 Points

I figured it out... your code should look like this...

SET @@sql_mode = ' ';
SELECT title, min(score) AS min_score,
    max(score) AS max_score, 
    avg(score) AS average 
FROM movies LEFT OUTER JOIN reviews
 ON movies.id = reviews.movie_id
 GROUP BY movie_id;
Daniel Muchiri
Daniel Muchiri
15,407 Points
Daniel Muchiri
Daniel Muchiri
15,407 Points

I had the same problem. This worked for me.

SELECT 
    title,
    MIN(score) AS minimum_score,
    MAX(score) AS maximum_score,
    IFNULL(AVG(score), 0) AS average_score
FROM
    movies
        LEFT OUTER JOIN
    reviews ON movies.id = reviews.movie_id
GROUP BY movie_id , movies.id;