How to use Grouping clause to handle NULLS in Oracle?

OracleSoftware & CodingProgramming

Problem:

You want to use GROUPING() with ROLLUP, CUBE and SETS functions.

Solution

The GROUPING() function accepts a column and returns 0 or 1. This function returns 1 when the column value is null and returns 0 when the column value is non null. However, GROUPING() is used only in queries that use ROLLUP or CUBE. GROUPING() is very useful when you want to display a value when a null would otherwise be returned.

GROUPING() with a Single Column in a ROLLUP

SQL to pass a column to ROLLUP

The last row in the below example will result total/sum of fees. This total is represented as NULL.

Example

SELECT class_id, SUM(fees)    FROM students GROUP BY ROLLUP(class_id) ORDER BY class_id;

Output

class_id
SUM
CL_MATH
39600
CL_PHY
12008
INS_CHAIRMAN
24000
INS_STAFF
28800
INS_VP
34000
138408
 

The class_id column for the last row is null. WE can use the GROUPING() function to determine whether this column is null, as shown in the following query.

The GROUPING() returns 0 for the rows that have non null class_id values and returns 1 for the last row that has a null class_id.

Example

SELECT  GROUPING(class_id) As grp, class_id, SUM(fees) FROM students GROUP BY ROLLUP(class_id) ORDER BY class_id;

Output

grp
class_id
total
0
CL_MATH
39600
0
CL_PHY
12008
0
INS_CHAIRMAN
24000
0
INS_STAFF
28800
0
INS_VP
34000
1
 
138408

We will now use the CASE statement to put a meaningful value.

Example

SELECT   CASE GROUPING(class_id)     WHEN 1 THEN 'Total fees'     ELSE class_id   END AS class_group,   SUM(fees) AS total FROM students GROUP BY ROLLUP(class_id) ORDER BY class_id;

Output

class_group
total
CL_MATH
39600
CL_PHY
12008
INS_CHAIRMAN
24000
INS_STAFF
28800
INS_VP
34000
Total fees
138408

GROUPING() with CUBE

Example

SELECT   CASE GROUPING(class_id)     WHEN 1 THEN 'Total Salaries'     ELSE class_id   END AS job_group,   SUM(fees) AS total_fees FROM students GROUP BY CUBE(class_id) ORDER BY class_id;

Output

job_group
total_fees
CL_MATH
39600
CL_PHY
12008
INS_CHAIRMAN
24000
INS_STAFF
28800
INS_VP
34000
Total Salaries
138408

The GROUPING SETS clause typically offers better performance than CUBE. Therefore, you should use GROUPING SETS as your first option rather than CUBE wherever possible.

GROUPING SETS Clause

Example

SELECT department_id, class_id, SUM(fees) AS total_fees FROM students GROUP BY GROUPING SETS(department_id, class_id) ORDER BY department_id, class_id;

Output

department_id
class_id
total_fees
60
 
28800
90
 
34000
100
 
51608
CL_MATH
39600
 
CL_PHY
12008
 
INS_CHAIRMAN
24000
 
INS_STAFF
28800
 
INS_VP
34000
 
 
24000
 

Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) CONSTRAINT  stu_last_name_nn  NOT NULL
    , email          VARCHAR2(40) CONSTRAINT  stu_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
      , join_date      DATE CONSTRAINT           stu_join_date_nn  NOT NULL
    , class_id         VARCHAR2(20) CONSTRAINT stu_job_nn  NOT NULL
    , fees           NUMBER(8,2)
    , professor_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     stu_fees_min CHECK (fees > 0)
    , CONSTRAINT     stu_email UNIQUE (email)
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;
raja
Updated on 05-Dec-2020 05:55:47

Advertisements