1/28/16

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.



0 comments:

Post a Comment