Introduction

An aggregate function performs a calculation on multiple values and returns a single value.

For example, you can use the AVG() aggregate function that takes multiple numbers and returns the average value of the numbers.

The following illustrates the syntax of an aggregate function:

aggregate_function(DISTINCT | ALL expression)

In this syntax:

• First, specify the name of the aggregate function e.g., AVG(). See the list of aggregate functions in the following section.
• Second, use DISTINCT if you want to calculate based on distinct values or ALL in case you want to calculate all values including duplicates. The default is ALL.
• Third, specify an expression that can be a column or an expression that involves column and arithmetic operators.

The aggregate functions are often used with the GROUP BY clause to calculate an aggregate value for each group e.g., the average value by the group or the sum of values in each group.

The following picture illustrates the SUM() aggregate function is used in conjunction with a GROUP BY clause:

Aggregate functions in MySQL are powerful SQL tools used to perform calculations on a set of values and return a single value. These functions are commonly used with the GROUP BY clause to group data into subsets before performing operations, but they can also be used without grouping.

List of Common Aggregate Functions

1. COUNT() - Counts the number of rows.
2. SUM() - Calculates the sum of values.
3. AVG() - Returns the average value.
4. MIN() - Retrieves the minimum value.
5. MAX() - Retrieves the maximum value.
6. GROUP_CONCAT() - Concatenates non-NULL values into a string.
7. VARIANCE() / VAR_POP() / VAR_SAMP() - Calculates variance.
8. STD() / STDDEV() / STDDEV_POP() / STDDEV_SAMP() - Calculates standard deviation.
9. BIT_AND() - Bitwise AND for a set of values.
10. BIT_OR() - Bitwise OR for a set of values.
11. BIT_XOR() - Bitwise XOR for a set of values.

Now let's dive into each one of these with examples.

Conclusion

Aggregate functions in MySQL are essential for summarizing and analyzing large datasets. They simplify complex queries by performing calculations like counting, summing, averaging, and more. Using them alongside GROUP BY and HAVING allows you to further refine and filter your data.