Group By Clause In ORACLE

• This clause is used to group the data over a specified column by eliminating duplicates.
•   Group by clause always arranged the data in ascending order.
•   The column which is used at group by clause, the same column can be used for the retrieving the data.
•   Group by clause will group the data over a single or multiple columns.

   Examples :=

   1)   Display total salary of all the three departments from the “emp” table.

   Ans : Select sum(sal) from emp

        Group by deptno;

       

   2)   Display deptno and total salary of all the 3 departments from “emp”table.

   Ans : Select deptno,sum(sal) from emp

           Group by deptno;

   3)  Display deptno and total salary, which is being paid to dept 20.

   Ans : Select deptno, sum(sal), count(*)

           From emp

           Where deptno=20

           Group by deptno;

   4)  Display job and total salary being paid on each job.

   Ans : Select job, sum(sal)

           From emp

           Group by job;

   5)  Display deptno,total salary,average salary, minimum salary  and number of employees in each dept.

   Ans :  Select deptno, sum(sal), avg(sal),  min(sal)

           Count(*) from emp

           Group by deptno;

   6)  Display deptno, job and total salary being paid on each group of deptno and job.

   Ans  : Select deptno,job, sum(sal) from emp

           Group by deptno,job;

7) Select deptno,sum(sal) avg(sal) from emp where job='CLERK'
    group by deptno;