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
Gabriel E
8,916 PointsCan I get some help on this SQL practice session?
I'm wrapping up my SQL chapter, and I'm stuck on the last 2 challenges. If you follow this link, launch the SQL playground, and look at Movie Average Rating, and Min and Max rating tasks, which are the last two, you'll see what I need help on. I'm just a little wobbly on how to get the AVG and ROUND the ratings from the movies database. https://teamtreehouse.com/library/reporting-with-sql/aggregate-and-numeric-functions/practice-session
Gabriel E
8,916 PointsAha! Haven't exactly tried it yet, but would I GROUP BY the movie_id?
Gabriel E
8,916 PointsHey, I think I just got it. I used this code, but it only returned 3 movies. But that's good, right? Anyway, it returned the 3 movie ids and then rounded the decimal to just 3.3.
SELECT ROUND(AVG(rating),1) AS movie_rating FROM reviews GROUP BY movie_id;
Gabriel E
8,916 PointsAnd again, sorry one last comment! :) I went ahead and did the last task, Min and Max ratings, and since I had a now-solid understanding of averaging, rounding, and grouping by the movies, I was able to Group by the movie ids and make 2 columns: one for min, one for max, and sure enough, I got 2 columns of smallest to largest movie ratings for each movie that was reviewed! Can you tell me if this code, which is what I used, is the most efficient? Of course, I didn't need to alias, or put it in desc order, but I just wanted to make it more orderly. Thanks!
SELECT MIN(rating), MAX(rating) FROM reviews GROUP BY movie_id ORDER BY movie_id DESC;
John Ham
5,958 PointsThat's great to hear that you figured it out!
Next, see if you can figure out how to get the movie name in there instead of the movie_id value. When dealing with SQL, you'll often have to join to other tables in order to match up a name with something else...like Customer and Order.
Efficiency isn't something I can comment on, but the way you've done it is the way I would've done it.
Have a great day!
2 Answers
John Ham
5,958 PointsHi Gabriel,
I see what you're talking about now...BTW...thanks for posting the link with your question. It made it easier to see what question you were trying to solve.
You're almost there! What the avg/round function will work on an aggregate of rows in your result set. What you need to do now is make sure your result set is reflecting the kind of data you want to have aggregated.
You're aggregating on ratings...and that part is correct...but you'll want to "group by" some other field...Think about that.
SELECT <some field you want to group by>, ROUND(AVG(rating), 1) FROM <some table/s> GROUP BY <some field you want to group by>
When you figure that part out...take note of your query...It's reading from a single table called "reviews". You can join to the movies table and pick up the name of the movie in your result set. Let me know if you're having trouble with this.
John Ham
5,958 PointsHi Gabriel,
You can nest functions so that the output of one is used as the input of another.
Example: round(avg(<some field>),<decimal places>)
The avg(<some field>) function will get you the average value of some field. It is being used as the input of the round function.
The round(<some value>, <decimal places>) function will get you the rounded value to the number of decimal places you indicate.
Hope this helps!
Gabriel E
8,916 PointsGabriel E
8,916 PointsWell, this is what I did:
SELECT ROUND(AVG(rating), 1) FROM reviews;. Now I'm getting a returned value of 3.8. But that's what I got for the overall user ratings. Shouldn't I be getting more than one, for each movie?