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 Reporting with SQL Aggregate and Numeric Functions Summing Values

Harris Handoko
Harris Handoko
3,932 Points
Posted by Harris Handoko
Harris Handoko
3,932 Points

Why is the WHERE clause required here instead of the HAVING clause?

So I entered the following code:

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

and it says You're missing the WHERE clause.

And so, I changed it to:

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

and it worked.

In retrospect, when I used HAVING in the AVG(rating) challenge task, it worked:

SELECT AVG(rating) AS "average_rating" FROM reviews GROUP BY movie_id HAVING movie_id = 6;

I already read the other post, but I don't think it explained this inconsistency. Please help, thanks!

1 Answer

Steven Parker
Steven Parker
229,783 Points
Steven Parker
Steven Parker
229,783 Points

When you filter directly on columns, use WHERE. But to filter on the result of aggregate functions (like AVG) you would use HAVING.

Also, this particular challenge doesn't require the use of GROUP BY. That clause can be left out entirely.

Harris Handoko
Harris Handoko
3,932 Points
Harris Handoko
Harris Handoko
3,932 Points

Oh right! That worked too. Thanks for the explanation!