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

Jan Lundeen
Jan Lundeen
5,887 Points
Posted by Jan Lundeen
Jan Lundeen
5,887 Points

Wrote query that totals up ratings in the reviews table. Error - missing WHERE clause. WHERE and GROUP BY don't mix

Hi,

In the Reporting with SQL class, I'm running into some issues with a query to totals all ratings in a movie review table(see Challenge Task 1 of 1). I wrote my query using the GROUP BY clause (see below). However, I got an error saying that I was missing the WHERE clause. I believe you cannot use WHERE and GROUP BY in the same query., so I'm confused. Can you help me out with this?

Thanks,

Jan

================================================================ 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.

My query:

SELECT SUM(rating) AS "starman_total_ratings" FROM reviews GROUP BY id HAVING id = 6;

Error:

Bummer! You're missing the WHERE clause. ============================================================

2 Answers

jacksonpranica
jacksonpranica
17,610 Points
jacksonpranica
jacksonpranica
17,610 Points

Hey Jan,

I can see where the confusion can come in with GROUP BY and WHERE statements being used together. As the teacher said GROUP BY is what you should be using when doing aggregate functions in SQL. So using a SUM would cause you to believe that you should be using just GROUP BY and a HAVING statement.

I will do my best to explain what HAVING and WHERE do.

WHERE can most definitely be used with GROUP BY and it helps filter out the data "BEFORE" you GROUP BY. For example, if you are grouping the movies by their id, you will get the sum of the ratings for each movie ID. This means you would get movie id 1 sum(ratings), movie id 2 sum(ratings), movie id 3 sum(ratings), etc.

However, if you use a WHERE statement to only look at movie ID number 6 the only result the GROUP BY will return is movie id 6 sum(ratings). So SQL filtered the data with WHERE and then GROUPED BY the id which only results in one group of movie id 6.

Now HAVING helps you filter the data "AFTER" you GROUP BY. So lets say you did a GROUP BY without a WHERE and got your movie id 1 sum(ratings), movie id 2 sum(ratings), movie id 3 sum(ratings), etc. HAVING would allow you to set a criteria like only showing the sum(ratings) that are greater than 5/10. Basically HAVING filters your groups, but this challenge requires you to filter the data before you group.

Place a comment if you don't understand with which part and I'll do a better job.

Below is the answer to your challenge

Be careful on the challenge. In the description it says movie_id is the movie's id. ID is the id of the person rating it.

SELECT SUM(rating) AS "starman_total_ratings" FROM reviews WHERE movie_id = 6 GROUP BY movie_id;
Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Jackson,

That worked. Thanks! I wasn't exactly sure if id was for movies or the id of the person rating it. I read the challenge again and that makes sense.

I'm still a little confused why we can use WHERE and GROUP BY together. However, your query does work, so that proved to me that you can use it. On Andrew's video for the "Getting the Grand Total" section, I noticed that he got a Misuse of aggregate sum error when he used WHERE and GROUP BY together. He also said that he got this because the WHERE conditions filtered the result set or rows, before grouping things together. He also said that it was pointless to group by a user ID or any column if you're going to filter out a certain group of users or values based on a condition. I could have misunderstood what Andrew said.

From what you said, it looks like the key is that you need to use WHERE before GROUP BY. Is there anything else I'm missing?

Thanks,

Jan