Group by clause is used to arrange similar data items into set of logical groups .
group by clause automatically reduces number of rows in each group.
group by clause is used in select statement.

Syntax:

Select columnname ......
 From tablename 
Group by columnname;

Example:

1) Write a query to display number of rows in each department from emp table by using GROUP BY clause.

SQL> Select deptno,count(*) from emp GROUP BY deptno;

Output:

  DEPTNO       COUNT(*)
---------   ----------- 
       10             3 
       20             5
       30             6

2) Write a query to display number of employees in each job from emp table by using GROUP BY clause.

SQL> Select job,count(*) from emp group by job;

Output:

JOB               COUNT(*)
-----------   -------------
CLERK                   4
SALESMAN                4
PRESIDENT               1
MANAGER                 3
ANALYST                 2

3) Write a query to display min , max salaries from each department by using emp table through GROUP BY clause.

SQL> Select deptno, min(sal),max(sal) from emp group by deptno;

Output:

DEPTNO     MIN(SAL)      MAX(SAL)
------  -----------   -----------   
   30          950           2850
   20          800           3000
   10         1300           5000

Note: 

In all relational databases we can also use group by clause without using group functions.

Example:

SQL> Select deptno from emp group by deptno;

Output:

DEPTNO
------
    10  
    20
    30

Rule:

1) other than group function columns specified after select statement then those all columns must be specified after group by clause otherwise Oracle server returns an error i.e. not a group by expression.

Example:

SQL> Select deptno,job,sum(sal) from emp group by deptno,job;

SQL> Select deptno,job from emp group by deptno,job;

Group by clause execution:

In Oracle when ever we are submitting group by clause query then Oracle server take specified columns from group by clause And also arrange similar data items into set of logical groups from the group by clause column and also automatically reduces number of rows in each group and then that result is internally stored in result set table and then only Oracle server selects columns from this result set table based on specified columns from select list.

Example:

SQL> Select deptno from emp group by deptno;

DEPTNO
------       
    10
    20
    30

Execution process :

• Step 1 : group by deptno

DEPTNO

                 10
10 ------------->10
30               10            

20-------------> 20
10               20
20               20

10-------------->30

• Step 2 : Select deptno from

DEPTNO
------       
    10
    20
    30

Step 1:

SQL> Select sum(sal) from emp;

Output: 30925

Step 2:

SQL> Select deptno,sum(sal) from emp; 

Output: Error: not a single-group group function.

Solution:

SQL> Select deptno,sum(sal) from emp group by deptno;

Output:

DEPTNO          SUM(SAL)
------       -----------
    30              9200
    20             12075
    10              9650

Q) Write a query to display those departments having more than 3 employees from emp table by using group by clause.

SQL> Select deptno,count() from emp  group by deptno having count() >3;

Output: Error 

Solution:

SQL> Select deptno,count() from emp  group by deptno having count() >3;

Output:

DEPTNO     COUNT(*)
------  ----------
    30           6
    20           5