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.