Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Thursday, 31 March 2016

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.
Share this article :

0 comments:

Post a Comment