Understanding HAVING vs WHERE in SQL: Filter Grouped Data

Key Takeaways

that was excellent work we've combined sorting and grouping next we will combine filtering with grouping in SQL we can't filter aggregate functions with wear Clauses for example this query attempting to filter the title count is invalid that means that if we want to filter based on the result of an aggregate function we need another way groups have their own special filtering word having for example this query shows only those years in which more than 10 films were released the reason why groups have their own keyword for filtering comes down to the order of execution we've written a query using many of the key words we have covered here this is their written order starting with select from films where the certification is G PG or PG13 Group by certification having the title count be greater than 500 order by title count and limit to three in contrast the order of execution is from where Group by having select order by and limit by reviewing this order we can see where is executed before Group by and before any aggregation occurs this order is also why we cannot use the Alias with having but we can with orderby where filters individual records while having filters grouped records we'll walk through two business questions here to show how to translate them into the correct filter the first question is what films were released in the year 2000 this question does not indicate any sort of grouping it asks to see only the titles from a specific year and can therefore be written as Select Title from films where release year equals 2000 the second question is in what years was the average film duration over 2 hours straight away we can see this question has a few more layers let's break down the question and query into smaller easier to understand steps this question requires us to return information about years so we select the release year from the film's table next it asks for the average film duration which tells us we need to place average duration somewhere since we do not need to provide any additional information around the duration on its own it is unlikely we need to perform the aggregation within the select Clause so we'll try the having Clause instead the last part of the question indicates we need to filter on the duration since we can't filter Aggregates with wear this supports our theory about using having finally we need to add a group by into our query since we have selected a column that has not been aggregated recall the aggregate function will convert the duration values into one average value going back to the start of our question we're interested in knowing the average duration per year so we group it by release year and there we have it [Music]