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.
ROLLUP GROUPING CUBE
These are the enhancements
to the group by feature.
USING ROLLUP
This will give the
salaries in each department in each job category along wih the total salary for
individual departments and the total salary of all the departments.
SQL> Select
deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO
JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER
2450
10 PRESIDENT
5000
10 8750
20 ANALYST
6000
20 CLERK 1900
20 MANAGER
2975
20 10875
30 CLERK
950
30 MANAGER
2850
30 SALESMAN
5600
30 9400
29025
USING GROUPING
In the above query it will give the total
salary of the individual departments but with a
blank in the job column and gives the total
salary of all the departments with blanks in
deptno and job columns.
To replace these blanks with your desired
string grouping will be used
SQL> select decode(grouping(deptno),1,'All
Depts',deptno),decode(grouping(job),1,'All
jobs',job),sum(sal) from emp group by
rollup(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ---------------------------------- --------------
10 CLERK 1300
10 MANAGER
2450
10 PRESIDENT 5000
10 All jobs 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 All jobs 9400
All Depts All jobs 29025
Grouping will return 1 if the column which
is specified in the grouping function has been
used in rollup.
Grouping will be used in association with
decode.
USING CUBE
This will give the
salaries in each department in each job category, the total salary for individual
departments, the total salary of all the departments and the salaries in each job
category.
SQL> select
decode(grouping(deptno),1,’All Depts’,deptno),decode(grouping(job),1,’All
Jobs’,job),sum(sal) from emp group by
cube(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ------------------------------------ ------------
10 CLERK 1300
10 MANAGER
2450
10 PRESIDENT 5000
10 All Jobs 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All Jobs 10875
30 CLERK 950
30
MANAGER 2850
30 SALESMAN 5600
30 All Jobs 9400
All Depts ANALYST 6000
All Depts CLERK 4150
All Depts MANAGER 8275
All Depts PRESIDENT 5000
All Depts SALESMAN 5600
All Depts All Jobs 29025
No comments:
Post a Comment