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

Mysql aggregate functions; Through this tutorial, you will learn how to use the MySQL aggregate AVG, COUNT, SUM, MIN, MAX functions.

Using aggregate functions of MySQL, You can calculate average and sum value of the columns of the tables using AVG, SUM functions of MySQL. Also you can find minimum or maximum value of the given column of database table using min() and max() function.

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

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

MySQL COUNT() Function

In MySQL, the COUNT () function is used to return the count of given columns. When you need to count some records from the database table, at that time we use this COUNT function of MySQL. If there are no matching rows, the returned value is 0.

Syntax

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.

MySQL Count() Function Query Example

SELECT COUNT(first_name)
FROM users;  

The above example return the count of rows the given column name first_name from the database table.

MySQL SUM() Function

In MySQL, the SUM() function is used to return the total sum of given columns. When you need to sum of the column from the database table, at that time we use this SUM function of MySQL. If the return set has no rows, the SUM() function returns NULL.

Syntax

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.

MySQL SUM() Function Query Example

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

The above example return the SUM of rows the given column name working_hours from the database table employees.

MySQL AVG() Function

In MySQL, the AVG() function is used to return the average value given columns in query. When you need to average of the column from the database table, at that time we use this AVG function of MySQL. If the return set has no rows, the SUM() function returns NULL.

Syntax

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.

MySQL AVG() Function Query Example

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

The above example return the calculate average of the given column name login_hour from the database table.

MySQL MAX() Function

In MySQL, the MAX() function is used to return the maximum value of given columns in query. If you need maximum value of the column from the database table, at that time we use this MAX function of MySQL.

Syntax

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.

MySQL MAX() Function Query Example

SELECT MAX (login_hour) AS "loginMax"
FROM users

The above example return the MAXIMUM login time of the given column name login_hour from the database table.

MySQL MIN() Function

In MySQL, the MIN() function is used to return the mininum value of given columns in query. If you need minimum value of the column from the database table, at that time we use this MIN function of MySQL.

Syntax

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.

MySQL MIN() Function Query Example

 SELECT MIN (login_hour) AS "loginMin"  
 FROM users

The above example return the minimum login time of the given column name login_hour from the database table.

Conclusion

In this MySQL tutorial point – You have learned MySQL aggregate functions that include AVG, COUNT, SUM, MIN, MAX, with syntax, example.

Recommended MySQL Tutorials