Grouping with the HAVING clause - SQL Tutorial

- [Instructor] Now, in our last example, we were able to include the WHERE clause and apply filtering to our non-aggregated field. In other words, you were able to filter down to only the cities that started with L by way of our WHERE clause which was applied to the billing city which in our example here is the non-aggregated field. Now, at the same time, there are times where we will need to use criteria on fields that have been aggregated, for instance, like our average total here. So let's say we have a new twist in the request from WSDA Music Management. They have now asked us to find all average totals that are greater than $5. How would we go about doing this? But first, let's update our question. We want all average invoice totals greater than five bucks and let's include our dollar sign there. Okay, what are the average invoice totals that are greater than $5? Now, this is just an alteration of our present WHERE clause, which is presently searching for only the billing cities that begin with L. So let's remove this current criteria and instead include the new criteria which is the average total or the average invoice amount and we want only the ones that are greater than $5. Now let's observe our query result with this update. What has happened? We have this new error message being displayed to us. We have no result. We have a lot of red under the lines that we've placed here in our query editor. And if we look at our messages window, we do see a clear error. "Execution finished with errors. Misuse of aggregate." And it specifies the average aggregate function. So this error message tells us, at least in this case, that we cannot use the WHERE clause to create a condition based on an aggregate function. Now, if we want additional filtering based on the aggregate function, we do need to include a secondary filtering clause and this clause is referred to as the HAVING clause. Now, the HAVING clause always comes after the GROUP BY clause. So let's now modify our query to reflect this. So instead of having the WHERE clause, and let me just create some space so that we see things a little more clearly, let's now remove the WHERE clause altogether. And instead, we will include the HAVING clause. So one thing to keep in mind is that the HAVING clause always comes after the GROUP BY. So I'm going to close the gap of the GROUP BY, I'm going to create some space under it between the GROUP BY and the ORDER BY clause, and now I'm going to include the keyword HAVING. And now I'm going to include exactly what we had in our WHERE clause attempting to get our new filter, which in this case is the aggregate average of the invoice amount, which is the total. And we want only those values that were greater than $5. So here we have our updated statement. A little tough to see, but let's run it now and observe the result. We now have a new result here. No errors. And if we take a look at the average amount being displayed here, it's all over the $5. Now let me close the gaps in our SQL statement and take a look at the full SQL statement. So we have now a SELECT clause. We do have our FROM clause, but we do have our grouping as well. And our new clause called the HAVING clause is now included and we have now our aggregate function in this particular clause.