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.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.