Aggregate functions - SQL Tutorial

- [Instructor] Let's take a look at another type of function called aggregate functions. Aggregate functions turn a range of numbers into a single point of data based on a variety of mathematical operations. Now let's take a look at an example where we would use our invoice table to calculate the grand total. In other words, let's say we were tasked by WSDA Music Management to get our total global sales of all time. How would we go about constructing a SQL statement to do this? Well, let's first start with our usual from clause, and because we're interested in the total sales, this information is held inside of our invoice table. So let's specify the invoice table after our from, then let's go above our from and specify the select. Now we can introduce our sum aggregate function to calculate the grand total for us. So let's specify sum, and then within parentheses, we want to specify the actual column or field we intend to apply this aggregation to. And in this case, in the invoice table, we want to apply this to the total field. Now we could alias this as well and say total sales. Now let's run our newly constructed statement and observe the result. If we notice here, we have a total value of $3,329.46. Now this is now effectively responding to WSDA Music Management, their latest request, which is to generate the total global sales of all time. And here we have a report that is responding to this question. Now there are a number of other popular aggregate functions that we should get familiar with as well. So let's go ahead and modify this present statement to include a few more aggregate functions. Let's introduce the average function, which is AVG, and then we'll perform the average on the same column which is the total. Let's alias this as average sales. Now let's take a look at this. Let's rerun our statement, and we now see we have a new column with the average sales. Okay, let's introduce a few more aggregates. Let's go with the max aggregate function, and we'll do this to the same field again, and we could call this the maximum sale. Now let's run this statement and we have now $1,000.86 as our maximum sale amount. Let's include the minimum aggregate function, and we'll call this minimum sale. Last but not least, let's also introduce one more aggregate function which is the count. With the count, we can count every single row within our invoice table. To do this, we're going to do something a little slightly different which is to just put a star in between the parentheses, and we will call this the sales count. Okay, with this, let's go ahead and now run our statement. And now we have the various values that are being triggered via the use of our various aggregate functions. We have total sales, average sales, maximum sale, minimum sale, and a total count of sales. Now this is a very good use of aggregate functions. We're going to take a look at one more use of our functions and see how we can make them a little more efficient by doing what's referred to as nesting them.