MySQL Aggregate Function

Introduction to MySQL Aggregate Function

Mostly in data query language, we use these aggregated functions with SELECT statements. An aggregate function performs a calculation on multiple values and returns a single value like the sum of all values, maximum and minimum among certain groups of values.

Example: If we have to calculate the total sales of a product in a month, then we have to use the ‘SUM’ function for adding up all the sales values. In the same manner, as using ‘MAX’ and ‘MIN’ functions, we can get the highest and lowest sale of that particular month. Aggregate functions ignore NULL values except ‘COUNT’ function. Count function returns the total number of observations. HAVING clause is used along with GROUP BY, for filtering query using aggregate values.

Top 11 Aggregate Functions

Here are some MySQL aggregate functions which are explained below:

1. AVG() Function
2. COUNT() Function
3. Sum() Function
4. Max() Function
5. MIN() Function
6. DISTINCT() Function
7. GROUP_CONCAT() Function
8. VAR() Function
9. STDEV() Function
10. BIT_AND() Function
11. BIT_OR() Function

Section	Name	Marks
Sec-A	Stewart	90
Sec-B	Vince	86
Sec-C	John	94
Sec-A	Michelle	78
Sec-C	Robin	60
Sec-A	Sara	86
Sec-B	Peter	92
Sec-C	Ian	89
Sec-A	David	76

1. AVG() Function

This is an average function. It function calculates the average value for a set of values. It ignores null values in calculation.

For getting average marks of all the students.

Query:

SELECT AVG(marks) AS avg_marks FROM student;

Output:

If we want the average mark of students for each section, then we can use AVG() with GROUP BY function.

Query:

SELECT section, AVG(marks) AS avg_marks FROM student GROUP BY section;

Output:

2. COUNT() Function

The COUNT() function returns the value of a total number of observations/total number of values in a set of values.

If we perform this function in the above set of example for getting the number of students,

Query:

SELECT COUNT(name) AS total_students FROM student;

Output:

For getting the student count in each section,

Query:

SELECT section, COUNT(name) AS total_students FROM student GROUP BY section;

Output:

3. Sum() Function

The SUM() function returns the sum of all values in a set. To get the sum of marks of all the students,

Query:

SELECT SUM(marks) AS total_marks FROM student;

Output:

Sum of marks of all students section-wise,

Query:

SELECT section, SUM(marks) AS total_marks FROM student GROUP BY section;

Output:

4. Max() Function

The max() function returns the maximum value in a set of values. To find the highest scorer in the exam from student database, below query can give us the desired output:

Query:

SELECT name, MAX(mark) AS highest_mark FROM student;

Output:

In the same process, we can find out the maximum mark secured from each section.

Query:

SELECT section, name, MAX(mark) AS highest_mark FROM student GROUP BY section;

5. MIN() Function

MIN() function returns the lowest value from the set of values. This doesn’t consider the null values. Lowest scorer among the students,

Query:

SELECT name, MIN(mark) AS lowest_mark FROM student;

Output:

This also can be used with GROUP BY function.

6. DISTINCT() Function

This function mostly we use with COUNT function to get the number of unique values in the set of values. We can also simply use this DISTINCT function to get the unique values.

Query:

SELECT DISTINCT (section) FROM student;

Output:

Query for using DISTINCT with COUNT() function.

Query:

SELECT COUNT(DISTINCT(section)) FROM student;

Output:

7. GROUP_CONCAT() Function

This is used to concat all the string values of one attribute, merge into one index.

Query:

SELECT GROUP_CONCAT(name SEPARATOR ‘;’) FROM student;

Output:

Like this GROUP_CONCAT() we also use another function CONCAT(), which merges 2 sets of string values into a new column.

Example:

If in this name database we use the function CONCAT(),

Query:

SELECT first_name, last_name, CONCAT(first_name,’ ‘,last_name) as full_name FROM name;

Output:

8. VAR() Function

This variance function returns the population standard variance of the specified column.

Variance is a measurement of the spread between the numbers in a dataset. It is calculated by how far each number is from the mean and therefore from each number of the set.

Variance:

Query:

SELECT VAR(mark) AS variance FROM student;

Output:

9. STDEV() Function

Standard deviation is the measure of the amount of variation or dispersion of a set of values. This express how much the member of a group differ from the mean value of the group. This is calculated by taking the square root of the variance.

This function returns the population standard deviation of the specified column.

Query:

SELECT STDEV(mark) AS std_deviation FROM student;

Output:

10. BIT_AND() Function

This function returns the bit-wise AND of specified rows. This returns the same data type whichever is passed through argument.

If every row has 1 in the bit position, then only it will return 1, otherwise, it returns 0.

Query:

SELECT BIT_AND( CAST(row_value VariableBIT) ) FROM student.list('0001,0111,0100,0011');

Output:

11. BIT_OR() Function

Returns the bit-wise OR of the specified expression for each group of rows.

Query:

SELECT BIT_OR( CAST(row_value AS VariableBIT) )
FROM student.list('0001,0111,0100,0011');

The result of 0111 is determined as follows:

• A bitwise OR is performed between row 1 (0001) and row 2 (0111), resulting in 0111.
• A bitwise OR is performed between the result from the previous comparison (0111) and row 3 (0100), resulting in 0111.
• A bitwise OR is performed between the result from the previous comparison (0111) and row 4 (0011), resulting in 0111.

Conclusion

In the analysis perspective as well as in the extraction process, those aggregated functions are very important. Specifically, when we give a condition in a query or while using windows function, aggregated functions play major roles. Some functions like LIMIT, RANK, etc. and GROUP BY clause always comes with aggregated functions.

Recommended Articles

This is a guide to MySQL Aggregate Function. Here we discuss the top 11 different types of MySQL aggregate function and their implementation. You may also look at the following articles to learn more –

1. MySQL vs SQLite
2. Insert Command in MySQL
3. SELECT in MySQL
4. MySQL BETWEEN