Fundamentals of SQL - Basics with example - 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

1     Group the rows together based on group by clause.

2     Calculate the group functions for each group.

3     Choose and eliminate the groups based on the having clause.

4     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

 SET OPERATORS

TYPES

1     Union

2     Union all

3     Intersect

4     Minus

UNION

This will combine the records of multiple tables having the same structure.

Ex:

     SQL> select * from student1 union select * from student2;

UNION ALL

This will combine the records of multiple tables having the same structure but including duplicates.

Ex:

     SQL> select * from student1 union all select * from student2;

INTERSECT

This will give the common records of multiple tables having the same structure.

Ex:

     SQL> select * from student1 intersect select * from student2;

MINUS

This will give the records of a table whose records are not in other tables having the same structure.

Ex:

     SQL> select * from student1 minus select * from student2;