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

Databases

Posted by Adiv Abramson
Adiv Abramson
6,919 Points

My query returned results but I got a warning that something's wrong with my query

Can someone explain what I did wrong here with my query?

Adiv Abramson
Adiv Abramson
6,919 Points

I checked the box to add my code to this posting but that didn't work.

My query is

SELECT movie_id, AVG(score) AS average FROM reviews GROUP BY movie_id;

4 Answers

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

:white_check_mark: You are correct that the GROUP BY clause must have all of the fields in the SELECT clause except for aggregate functions.

:point_right: But... there's no requirement that the SELECT clause have all of the fields in the GROUP BY clause (though the results may seem a bit silly without them).

Adiv Abramson
Adiv Abramson
6,919 Points
Adiv Abramson
Adiv Abramson
6,919 Points

Yes. It's from the Database Foundations series. Thank you.

Adiv Abramson
Adiv Abramson
6,919 Points
Adiv Abramson
Adiv Abramson
6,919 Points

Here is the problem statement:

Group all reviews by "movie_id" and get the average "score" and alias it as "average".

Sergey Podgornyy
Sergey Podgornyy
20,660 Points
Sergey Podgornyy
Sergey Podgornyy
20,660 Points

Try this code:

SELECT AVG(score) AS average FROM reviews GROUP BY movie_id;
Brandon McClelland
Brandon McClelland
4,645 Points
Brandon McClelland
Brandon McClelland
4,645 Points

It sounds like you don't need to include the select movie_id piece. Doing so would cause the id to show up in your results, but the task is only asking to show the average.

Adiv Abramson
Adiv Abramson
6,919 Points

Thank you for your response. I will try that. But I thought that the GROUP BY clause had to have all of the fields in the SELECT clause except for aggregate functions. So I thought, if I want to group by movie_id, I must include it in the SELECT clause and in the GROUP BY clause. I seem to recall Microsoft Access displaying alerts to that effect on numerous occasions when I composed queries in that application.