MySQL Aggregate Function: SUM, AVG, MAX, MIN, COUNT

MySQL aggregate functions(SUM, AVG, MAX, MIN, COUNT); Through this tutorial, i am going to show how to use the MySQL aggregate functions ( AVG, COUNT, SUM, MIN, MAX ).

MySQL Aggregate Functions

There are types of MySQL aggregate functions; as follows:

• MySQL COUNT() Function
• MySQL SUM() Function
• MySQL AVG() Function
• MySQL MAX() Function
• MySQL MIN() Function

MySQL COUNT() Function

MySQLCOUNT () function is used to return the count of given columns.

Note that :- If there are no matching rows, the returned value is 0.

Syntax of MySQL COUNT() Function

The basic syntax of MySQL COUNT function is :-

SELECT COUNT (column_name) 
FROM table_name  
[WHERE conditions]; 

Params of COUNT function

• column_name :- This database is the column name in the table, you want to count it a value.
• table_name :- This is the table name of the database, where you want to retrieve records.
• WHERE conditions :- It is optional. This specifies the conditions that must be matched.

Example of MySQL Count() Function Query

To count some records from the database table, at that time need to use this COUNT function of MySQL; as follows:

SELECT COUNT(first_name)
FROM users;  

MySQL SUM() Function

MySQL SUM() function is used to return the total sum of given columns.

Note that :- If the return set has no rows, the SUM() function returns NULL.

Syntax of MySQL SUM() Function

The basic syntax of MySQL SUM function is :-

 SELECT SUM (column_name)  
 FROM table_name  
 [WHERE conditions]; 

Params of SUM function

• column_name :- This database is the column name in the table, you want to sum total it a value.
• table_name :- This is the table name of the database, where you want to retrieve records.
• WHERE conditions :- It is optional. This specifies the conditions that must be matched.

Example of MySQL SUM() Function Query

To sum of the column from the database table, at that time need use this SUM function of MySQL; as follows:

SELECT SUM (working_hours) AS "totalHours"  
FROM employees  
WHERE working_hours > 8;  

MySQL AVG() Function

MySQL AVG() function is used to return the average value given columns in query.

Note that :- If the return set has no rows, the SUM() function returns NULL.

Syntax of MySQL AVG() Function

The basic syntax of MySQL AVG function is :-

SELECT AVG (column_name)
FROM table_name  
[WHERE conditions]; 

Params of AVG function

• column_name :- This database is the column name in the table, you want get the average of given column.
• table_name :- This is the table name of the database, where you want to retrieve records.
• WHERE conditions :- It is optional. This specifies the conditions that must be matched.

Example of MySQL AVG() Function Query

To average of the column from the database table, at that time we use this AVG function of MySQL; as follows:

SELECT AVG (login_hour) AS "totalHours"
FROM users
WHERE login_hour > 8;  

MySQL MAX() Function

MySQLMAX() function is used to return the maximum value of given columns in query.

Syntax of MySQL MAX() Function

The basic syntax of MySQL MAX function is :-

SELECT MAX (column_name)  
FROM table_name  
[WHERE conditions]; 

Params of MAX function

• column_name :- This database is the column name in the table, you want get the MAXIMUM value of given column.
• table_name :- This is the table name of the database, where you want to retrieve records.
• WHERE conditions :- It is optional. This specifies the conditions that must be matched.

Example of MySQL MAX() Function Query

To get maximum value of the column from the database table, at that time we use this MAX function of MySQL; as follows:

SELECT MAX (login_hour) AS "loginMax"
FROM users

MySQL MIN() Function

MySQL MIN() function is used to return the mininum value of given columns in query.

Syntax of MySQL MIN() Function

The basic syntax of MySQL MIN function is :-

 SELECT MIN (column_name)  
 FROM table_name  
 [WHERE conditions]; 

Params of MIN function

• column_name :- This database is the column name in the table, you want get the MINIMUM value of given column.
• table_name :- This is the table name of the database, where you want to retrieve records.
• WHERE conditions :- It is optional. This specifies the conditions that must be matched.

Example of MySQL MIN() Function Query

To get minimum value of the column from the database table, at that time we use this MIN function of MySQL’; as follows:

 SELECT MIN (login_hour) AS "loginMin"  
 FROM users

Conclusion

MySQL aggregate functions tutorial; You have learned MySQL aggregate functions that include AVG, COUNT, SUM, MIN, MAX, with syntax, example.

Recommended MySQL Tutorials