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:
- AVG() Function
- COUNT() Function
- Sum() Function
- Max() Function
- MIN() Function
- DISTINCT() Function
- GROUP_CONCAT() Function
- VAR() Function
- STDEV() Function
- BIT_AND() Function
- BIT_OR() Function
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.
SELECT AVG(marks) AS avg_marks FROM student;
If we want the average mark of students for each section, then we can use AVG() with GROUP BY function.
SELECT section, AVG(marks) AS avg_marks FROM student GROUP BY section;
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,
SELECT COUNT(name) AS total_students FROM student;
For getting the student count in each section,
SELECT section, COUNT(name) AS total_students FROM student GROUP BY section;
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,
SELECT SUM(marks) AS total_marks FROM student;
Sum of marks of all students section-wise,
SELECT section, SUM(marks) AS total_marks FROM student GROUP BY section;
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:
SELECT name, MAX(mark) AS highest_mark FROM student;
In the same process, we can find out the maximum mark secured from each section.
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,
SELECT name, MIN(mark) AS lowest_mark FROM student;
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.
SELECT DISTINCT (section) FROM student;
Query for using DISTINCT with COUNT() function.
SELECT COUNT(DISTINCT(section)) FROM student;
7. GROUP_CONCAT() Function
This is used to concat all the string values of one attribute, merge into one index.
SELECT GROUP_CONCAT(name SEPARATOR ‘;’) FROM student;
Like this GROUP_CONCAT() we also use another function CONCAT(), which merges 2 sets of string values into a new column.
If in this name database we use the function CONCAT(),
SELECT first_name, last_name, CONCAT(first_name,’ ‘,last_name) as full_name FROM name;
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.
SELECT VAR(mark) AS variance FROM student;
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.
SELECT STDEV(mark) AS std_deviation FROM student;
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.
SELECT BIT_AND( CAST(row_value VariableBIT) ) FROM student.list('0001,0111,0100,0011');
11. BIT_OR() Function
Returns the bit-wise OR of the specified expression for each group of rows.
SELECT BIT_OR( CAST(row_value AS VariableBIT) )
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.
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.
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 –