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.

Databases Reporting with SQL Aggregate and Numeric Functions Getting the Grand Total

Bummer! 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

Hi Jason,

Thanks for clarifying what the challenge question was wanting..

SELECT SUM(rating) AS starman_total_ratings FROM reviews WHERE movie_id = 6;

Hi 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
Marcos Treviño Rodriguez
5,711 Points

Just had the same issue, but i was mixind id and Movie_ id =D. Thanks a lot for the answer

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

Jason Anello I thought Andrew said Where shouldnt be used in conjuction with functions, but rather having ?

Hi 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
Tommy Gebru
30,150 Points

Yeah i misunderstood the instructions and tried using the Starman string instead of movie_id of 6