102. GROUP BY AND HAVING

GROUP BY

Using group by, we can create groups of related information.

Columns used in select must be used with group by, otherwise it was not a group by expression.

Ex:

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

                        DEPTNO   SUM(SAL)

   ---------- ----------

        10       8750

        20      10875

        30       9400

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

                        DEPTNO  JOB         SUM(SAL)

  ---------- ---------   ----------

        10   CLERK            1300

        10   MANAGER      2450

        10   PRESIDENT   5000

        20   ANALYST       6000

        20   CLERK           1900

        20   MANAGER     2975

        30   CLERK             950

        30   MANAGER      2850

        30   SALESMAN    5600

HAVING

This will work as where clause which can  be used only with group by because of absence of where clause in group by.

Ex:

     SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) >

             3000;

                         DEPTNO   JOB            TSAL

   ----------  ---------      ----------

        10    PRESIDENT    5000

        20    ANALYST        6000

        30    SALESMAN     5600

SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) >

        3000 order by job;

                   DEPTNO    JOB          TSAL

 ----------  ---------    ----------

          20        ANALYST       6000

                    10        PRESIDENT   5000

     30        SALESMAN    5600

ORDER OF EXECUTION

Ø  Group the rows together based on group by clause.

Ø  Calculate the group functions for each group.

Ø  Choose and eliminate the groups based on the having clause.

Ø  Order the groups based on the specified column.