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
fot
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