SQL 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.