Aggregated subqueries - SQL Tutorial

- [Instructor] Now if you recall in our last chapter on functions, we used the group by statement to show the average subtotals of invoices by city. Now, what if we had a new ask about how each individual city was performing against the global average sales? Now, one way to answer this question would be to write a query that was able to display the average sales of each city right next to the global average. The query we write to display the average sales by billing city is identical to the one we wrote previously, with one exception. We also need to include a subquery in the select clause to calculate the global average. This way, we can compare the two values. Let's go ahead and build out our query. As we usually do, let's start with the from clause. And we want the invoice table. I'm going to go above the from and include our select. And let's now include the fields we want to see. We want to see the billing city. And now that I have included the billing city, let me go ahead and introduce the group by clause, which always comes after the from, group by. And let's group by that billing city, seeing that we're not going to aggregate this field. This order by billing city as well. And now, I'm going to return back up to our select statement here, or select clause and include the rest of our query. So, so far, we would like to display the billing city, as well as the average for each billing city. So let's go ahead and introduce our aggregate function here, which is the average. And we want to see the average total. Okay, so this takes care of some of the ask. So we want to display the average total for each billing city. However, the last twist is we want to see how each average is performing against the global average sales. So now, we need to include the global average sale as part of our result. So let's include one more column here. I'm going to put a comma, and let's include our subquery inside of our select. Now, as we said before with subqueries, we're going to start with an open and closed parenthesis. And in between that comes a full select statement. And in this case, we want the average. So let's go ahead and say select the average, and that's going to be the total from the invoice table. Let's take a look at our result. We now have our billing city. We now have an aggregated column, which is displaying our average, and now we have a subquery which also has an aggregate function, which is displaying our global average. Let's run this query. Okay. If we take a look, we now see that we have some values displaying for our average, and some other values displaying for our global average. Now, let's label this a little better so we can be a little cleaner in our result. So let's call our average total the city average. And I'm going to say as. And for our subquery, let's also alias this by calling it the global average. Now, let's rerun our statement. And we have a little cleaner titles, and makes a little more sense to read this now. So we have each city, and we have the city average that is for each city. Now, because we wanted to compare the global average against each city, we have the global average being repeated so that we can compare it side by side with each city's average. And this has been achieved by including a subquery inside of our select statement as we're doing here. And this effectively responds to the latest inquiry by WSDA Music Management, which is how is each individual city performing against the global average sale? And here, we have the response to this inquiry. Well done.