MySQL Count Sum Max Min AVG function
MySQL Count, Sum, Max, Min, AVG functions are also known as group functions as they are applicable on more than one row and used with SELECT statement. They are also known as multi-row functions. Syntax of each function is different so we will discuss one function at a time.
Count Function
count function is used to count the number of rows in a table or group. The general syntax of count
SELECT COUNT(*|columnName) from TableName
Sample Database to show the Working of count function
mysql> select * from student; +-------+---------+--------+-------+-------+---------+--------+ | admno | name | stream | marks | grade | fees | gender | +-------+---------+--------+-------+-------+---------+--------+ | 101 | rakesh | sci | 96.56 | A | 1850.35 | F | | 102 | udit | sci | 56.50 | B | 1850.35 | M | | 103 | mohit | bio | 46.50 | C | 1980.90 | M | | 104 | mannat | human | 67.50 | A | 1420.15 | F | | 105 | unnati | comm | 77.50 | B | 1650.75 | F | | 106 | nikunj | sci | 79.50 | A | 1850.35 | M | | 107 | pushkar | comm | 54.20 | C | 1650.75 | M | | 108 | pratham | comm | 84.80 | A | 1650.75 | M | | 109 | mohit | comm | 64.80 | B | 1650.75 | M | | 110 | urshavi | bio | 96.80 | A | 1980.90 | M | | 111 | punya | comm | 86.56 | B | 1650.75 | F | | 112 | vipul | comm | 88.32 | A | 1650.75 | M | +-------+---------+--------+-------+-------+---------+--------+ 12 rows in set (0.00 sec)
The commands to find out the total phone number in the above table maybe something like this
mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.00 sec) mysql> select count(phone) from student; +--------------+ | count(fees) | +--------------+ | 12 | +--------------+ 1 row in set (0.00 sec)
NOTE:
When ColumnName is used as a parameter in the count( ) function, it does not count the row that contains NULL values.
Sum() function
sum function finds out the sum of non-null values of the specified column.
Example
mysql> select sum(fees) from student; +-----------+ | sum(fees) | +-----------+ | 20837.50 | +-----------+ 1 row in set (0.00 sec)
Max() function
Max function fetch the largest value available in that column.
Example
mysql> select max(marks) from student; +------------+ | max(marks) | +------------+ | 96.80 | +------------+ 1 row in set (0.00 sec)
Min() function
Min function fetches the lowest value available in that column. Null value if available in that column then it does not participate in the result.
Example
mysql> select min(marks) from student; +------------+ | min(marks) | +------------+ | 46.50 | +------------+ 1 row in set (0.00 sec)
AVG() function
AVG function calculates the average value of all those non-Null values available in that column.
Example
mysql> select avg(marks) from student; +------------+ | avg(marks) | +------------+ | 74.961667 | +------------+ 1 row in set (0.00 sec)
These functions can be combined with MySQL Where Clause as well as a MySQL group by clause to refind our result.