MySQL Group Functions

MySQL Group Functions operates over number of values on a column and returns single value. Group functions are not allowed to use in WHERE condition.

MySQL is having following GROUP functions”:

max
min
avg
sum
count(*)
count(column_name)

For all examples of above MySQL Group functions, used below EMP table. You can also create by Create EMPLOYEE Table

max(): It returns maximum value from a column in the table.

mysql> select max(sal) from EMP; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.05 sec) mysql> select max(hiredate) from EMP; +---------------------+ | max(hiredate) | +---------------------+ | 1983-01-12 00:00:00 | +---------------------+ 1 row in set (0.04 sec) mysql> select max(ename) from EMP; +------------+ | max(ename) | +------------+ | WARD | +------------+ 1 row in set (0.02 sec) mysql> select max(deptno) from EMP; +-------------+ | max(deptno) | +-------------+ | 30 | +-------------+

min(): It returns minimum value from a column in the table.

mysql> select min(sal) from EMP; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ 1 row in set (0.00 sec) mysql> select min(hiredate) from EMP; +---------------------+ | min(hiredate) | +---------------------+ | 1980-12-17 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> select min(ename) from EMP; +------------+ | min(ename) | +------------+ | ADAMS | +------------+ 1 row in set (0.00 sec) mysql> select min(deptno) from EMP; +-------------+ | min(deptno) | +-------------+ | 10 | +-------------+

avg(): This function returns average from number data type column.

mysql> SELECT AVG(SAL) from EMP; +-------------+ | AVG(SAL) | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec)

sum(): This returns total from number data type column.

mysql> SELECT sum(SAL) from EMP; +----------+ | sum(SAL) | +----------+ | 29025.00 | +----------+ 1 row in set (0.00 sec)

count(*): This function returns number of rows in the table.

mysql> select COUNT(*) from EMP; +----------+ | COUNT(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)

count(Column_name): This will return number of not null values in a column.

mysql> select count(comm) from EMP; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)