Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
Bummer! Your query didn't perform the correct sum calculation. [RESOLVED]
Link to challenge:
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):
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?)
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...?
Thanks for clarifying what the challenge question was wanting..
SELECT SUM(rating) AS starman_total_ratings FROM reviews WHERE movie_id = 6;
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
Just 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
Tommy Gebru30,150 Points
Yeah i misunderstood the instructions and tried using the Starman string instead of movie_id of 6