MySQL GROUP BY Clause

MySQL GROUP BY Clause is used to arrange similar data items into set of logical groups. Whenever we are using group by clause database server selects similar data items from a table column and then reduces number of data item in each group.

MySQL GROUP BY Clause Syntax:

SELECT column_name1,..... from table GROUP BY column_name;

Examples:

For all examples , used below EMP table. You can also create by Create EMPLOYEE Table

+-------+--------+-----------+------+---------------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-28 00:00:00 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+---------------------+---------+---------+--------+

1. Display number of employees in each department from employee table using GROUP BY clause.

mysql> select deptno, count(*) from EMP GROUP BY deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+ 3 rows in set (0.00 sec)

2. Display number of employees in each job from employee table using GROUP BY.

mysql> select JOB, count(*) from EMP GROUP BY JOB; +-----------+----------+ | JOB | count(*) | +-----------+----------+ | ANALYST | 2 | | CLERK | 4 | | MANAGER | 3 | | PRESIDENT | 1 | | SALESMAN | 4 | +-----------+----------+ 5 rows in set (0.02 sec)