We’ve covered several different features in SQL so far, and in this topic we will cover aggregate functions. We’ve tried out some functions in past lessons that operate on the value of one field. Aggregate functions are functions that operate on multiple values and return one value as a result.

There are list of different aggregate functions that are supported in MySQL, but let’s start with the five most commonly used aggregate functions.

AVG() – calculates the average of values within the group
COUNT() – counts the number of rows in a group, including empty rows
MAX() – finds the highest value within the group
MIN() – finds the smallest value within the group
SUM() – provides the sum of all the values within the group

First, hope you have noticed that the operation of all five main aggregate functions are pretty intuitive. Averages, counts, maxs, mins and sums are concepts that we commonly use, and yes, all of them mean what you expect them to mean. So you’re in the right path if you’re getting excited to unleash these functions with all your ideas on how you could utilize these functions!

Another thing you might have noticed is the use of the word “groups.” This will be more easily understood as we try out some examples in the next topic, but basically we are able to set up groups within the data that we are working on to operate these functions. As we get more familiar with setting up groups, the better we will get in providing analytics and meaningful insights from the data.

For the expression that goes into the parenthesis, for many cases, it could be the name of a field. But it could also more than one field involved, or there could be a nested function within it.

One last thing to point out is that within the parenthesis, before the expression you could provide and argument of either “ALL” or “DISTINCT.” For ALL, the function will make the calculations with all the values within the group and with DISTINCT, the function will perform the operations with just the distinct values. If no argument is given, by default, it will perform the operation with all the values.

There are several other aggregate functions that are supported additional to the main five such as the ones below.

GROUP_CONCAT() – concatenates strings within a group
STDEV() – produces the standard deviation of the values within the group.

Hope you’re excited to try out some of these aggregate functions!

You can watch a video of a brief walk through of this topic here.