MySQL Aggregate function

The aggregation function works on the multiple values and returns single value output.  It makes the advanced and complex operation simple. The summation (SUM), MAX, AVG, MIN, COUNT functions are examples of the aggregate function.

• SUM: The SUM function perform the addition of the table values.
• MAX: The MAX function determines the maximum value of the data set.
• AVG: The AVG or average function displays the average value of the data set.
• MIN: This MIN function shows the minimum value of the data set.
• COUNT: The COUNT function determines the count of the data set.
• group_concat(): The group_concat function displays the concatenated string.
• first(): The first function shows the first value of an expression.
• last(): The last function displays the last value of an expression.

Syntax

The following syntax is the basis of all aggregate functions.

Aggregate_Function(DISTINCT | ALL)

Description of the aggregate function syntax:

• The "Aggregate_Function" chooses the required function for MySQL data operation.
• This function uses the required value or the required column of the table.
• Sometimes, the aggregation function uses the "DISTINCT" modifier or the "ALL" modifier.
• The modifier calculates the value of the column and returns the required value of the table column.

Application of the MySQL aggregate function

The aggregation functions need to calculate the maximum, minimum, and relevant values. The following are applications that required aggregate functions to make their uses friendly.

• The aggregate function is used in school and college management system applications. Here, you can find the highest and lowest marks, summarized mark sheet, and attendance data.
• The movie and entertainment applications use this function. You can filter movies and dramas as per high and low reviews. You can show the prices of the movie using the aggregate function.
• The aggregate function is used in business applications like online business and eCommerce sites.
• It is also used in corporate offices to handle data of the employees and management system.

Prerequisite for the MySQL aggregate function

• Use database in the MySQL data management system interface. If a database is not available, then create it with the SQL query.
• Create a new table with the required column, keys, data type, and constraints.

mysql> create table phone_directory (  

    pincode int NOT NULL,

    name varchar(100) NOT NULL,    

    phone_number bigint,  

    received_date date  

);  

• Insert the value in the table.

mysql> insert into 

phone_directory(pincode, name, phone_number, received_date) 

VALUES    

(411341, 'Ram', 9123011122, '2021-07-01'),  

(412343, 'Sam', 7823019126, '2021-07-03'),  

(412343, 'Tom', 9123056173, '2021-07-05'),  

(413212, 'John', 9425061192, '2021-07-07'),  

(413213, 'Pam', 9128029132, '2021-07-09'),  

(413214, 'Adam', 9823011728, '2021-07-11'); 

• Show the output table, structure, and its data using the below query.

mysql> select * from phone_directory;

SUM() function

The SUM() function shows the addition of the table values. The SUM() function does not return a null value. Here, the sum function shows the summation of all values in the table. If the table displays a null value, then the row is unavailable in the table column. This function works on numerical data type values.

Example: the "SUM" function's example and the output shows below.

mysql > Select SUM(phone_number) AS summation from phone_directory;

OUTPUT

The above image shows the summation of the exam hour column. The total addition of the hours displays the "10" value.

MAX() function

The MAX() function returns the maximum value of the data set. This function works on numerical data to display the largest value.

Example: the "MAX" function's example and the output shows below.

mysql > Select MAX(phone_number) AS maximum from phone_directory;

OUTPUT

This output shows the largest number of phone numbers in the table. The function compares the entire row with each other.

AVG() or average() function

The average function calculates the average value of the table data. This function works on numerical values because of arithmetic operation. If the table does not contain any value, then the average function shows the null output.

Example: the "average" function's example and the output shows below.

mysql > Select AVG(phone_number) AS average from phone_directory;

OUTPUT

The above output shows an average number of the phone_number column. This function returns a decimal value as output because of average calculation.

MIN() function

The MIN() function determines the minimum value of the data set. This function works on numerical data type values. If the table displays a null value, then the column does not contain values.

Example: the "MIN" function's example and the output shows below.

mysql > Select MIN(phone_number) AS minimum from phone_directory;

OUTPUT

The above output shows the lowest number of the table column. You can see the lowest phone number of the column shows as output.

COUNT() function

The count() function works for the counting data set. This function displays available rows of the table.  The COUNT function operates string, numerical, and other data type's columns. If the table is empty, then the count function displays the null output.

Example: the "COUNT" function's example shows below.

mysql > Select COUNT(phone_number) AS count from phone_directory;

OUTPUT

The count function shows the "6" data set as output. This function returns the available row of the given column.

FIRST function

The first function displays the first value of the column. This function uses the "LIMIT" clause with a one-row condition.  If a table is empty, then output displays a null value. You do not need a special function or keyword for data operation.

Example: the "FIRST" function's example and the output shows below.

mysql> SELECT phone_number from phone_directory LIMIT 1;

OUTPUT

The output image shows the first value of the column. The "phone_number" column shows the first row of the given table. You can modify this function with other clauses.

LAST function

The "last" function displays the last value of the table. This function determines the last row of the table column. If the table column is empty, then the output shows a null value. This function requires the "ORDER BY" clause in descending order. The "LIMIT" clause must be containing one (1) value. You do not need a special method and function for the last function.

Example: the "LAST" function's example and the output shows below.

mysql> SELECT phone_number from phone_directory ORDER BY phone_number DESC LIMIT 1;

OUTPUT

The above phone number shows you the last row of the table. This value contains the last in the phone number column.

Examples the MySQL aggregate function

The following query shows a different type of MySQL aggregation function. You can understand this function using the below examples. Here, you get the basic information and operation of the aggregation function.

mysql > Select SUM(phone_number) AS SUM,
	  MAX(phone_number) AS MAX,
	  MIN(phone_number) AS MIN,
	 AVG(phone_number) AS Average,
 COUNT(phone_number) AS COUNT from phone_directory;

OUTPUT

The above output shows the aggregation function and its value. You can see the difference between the function and its operations.