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
james white
78,399 PointsBummer! Your query didn't perform the correct sum calculation. [RESOLVED]
Link to challenge:
https://teamtreehouse.com/library/reporting-with-sql/aggregate-and-numeric-functions/summing-values
Challenge Task 1 of 1
We're in a movie database. There's a reviews table with the columns of id, movie_id, username, review and rating.
The movie "Starman" has the id of 6. Movie ids are found in the movie_id column in the reviews table. Write a query that totals up all ratings in the reviews table. Alias it as starman_total_ratings.
SELECT SUM(rating) AS starman_total_ratings FROM reviews WHERE rating > 0;
The output (in a boxed grid):
starman_total_ratings
19
So what should I have gotten for the 'correct sum calculation' ?
Here's another try (same error and output):
SELECT SUM(rating) AS starman_total_ratings FROM reviews WHERE rating < 100;
I also tried it without the WHERE clause. The output was still the same but the error was:
Bummer! You're missing the WHERE clause.
However no where in the challenge question does it instruct using the WHERE clause
(was that supposed to be assumed?)
Also tried:
SELECT SUM(rating) AS starman_total_ratings, movie_id FROM reviews WHERE rating < 100 GROUP BY movie_id;
which produced a nice two column table but the same error:
Bummer! Your query didn't perform the correct sum calculation.
Obviously I missing something...?
5 Answers
james white
78,399 PointsHi Jason,
Thanks for clarifying what the challenge question was wanting..
SELECT SUM(rating) AS starman_total_ratings FROM reviews WHERE movie_id = 6;
Jason Anello
Courses Plus Student 94,610 PointsHi James,
Your first attempt is closest but your WHERE condition is incorrect. We're not trying to total up all ratings that are greater than 0. We're trying to total up all ratings specifically for the Starman movie.
You are told in the challenge that the movie id is 6 and this corresponds to the movie_id column.
So your WHERE condition should be movie_id = 6
This will sum up the ratings as long as the movie_id is 6
Marcos Treviño Rodriguez
5,711 PointsJust had the same issue, but i was mixind id and Movie_ id =D. Thanks a lot for the answer
lukej
34,222 PointsJust to clarify this again in case someone else is having a similar problem: Using 'WHERE' or 'HAVING' is not dependent on whether you are using a function in your SQL statement or not. You use either 'WHERE' or 'HAVING' depending on what kind of cells you are referencing. You can reference two kinds of cells:
- (1) cells that are already present in the database --> use 'WHERE'
- (2) cells that are created by functions in your current SQL statement --> use 'HAVING'
SELECT SUM(price) FROM orders WHERE category = "clothes"
-- here we are referencing a cell that already exists in the database therefore we use WHERE
SELECT SUM(price) AS sum FROM orders GROUP BY category HAVING sum > 1000;
-- now we are referencing a 'cell' or variable that was created by the SUM() function therefore we have to use HAVING
adsdev
15,583 PointsJason Anello I thought Andrew said Where shouldnt be used in conjuction with functions, but rather having ?
Jason Anello
Courses Plus Student 94,610 PointsHi adsdev,
Can you link to the video and approximate time where he said this so I can see the context?
In the previous stage, he used the REPLACE function with the WHERE clause.
https://teamtreehouse.com/library/reporting-with-sql/working-with-text/replacing-portions-of-text
Tommy Gebru
30,164 PointsYeah i misunderstood the instructions and tried using the Starman string instead of movie_id of 6