Oracle SQL Aggregated CASE Expressions Vs PIVOT

I had a question on my mind that, which is the most efficient way to print totals and subtotals in a single row. 

I was thinking PIVOT would be the significant way compare to case statement. Let's see which is more efficient way to do that?

Here I am giving an example to write a SQL query that performs a aggregate functions(count, sum, avg and etc) of a column while including case statement.

Step 1: Create DEPT table which will be the parent table of the EMP table.

DROP TABLE dept;

------------------------------------------------------------

CREATE TABLE dept

 (

   deptno NUMBER(2,0),

   dname  VARCHAR2(14 BYTE),

   loc    VARCHAR2(13 BYTE),

   CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE

  );

Step 2: Insert row into DEPT table using named columns.

INSERT INTO dept (deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO dept (deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept (deptno,dname,loc) VALUES (30,'SALES','CHICAGO');

INSERT INTO dept (deptno,dname,loc) VALUES (40,'OPERATIONS','BOSTON');

Step 3: Create the EMP table which has a foreign key reference to the DEPT table. The foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO column in the DEPT table.

DROP TABLE emp;

------------------------------------------------------------

CREATE TABLE emp

  (

    empno    NUMBER(4,0),

    ename    VARCHAR2(10 BYTE),

    JOB      VARCHAR2(9 BYTE),

    mgr      NUMBER(4,0),

    hiredate DATE,

    sal      NUMBER(7,2),

    comm     NUMBER(7,2),

    deptno   NUMBER(2,0),

    CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE,

    CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno) ENABLE

  );

Step 4: Insert row into EMP table using named columns.

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,NULL,20);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,NULL,20);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,NULL,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,NULL,10);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,NULL,20);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-NOV-81','DD-MON-RR'),5000,NULL,10);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,NULL,20);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,NULL,30);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,NULL,20);

INSERT INTO emp (empno,ename,JOB,mgr,hiredate,sal,comm,deptno) VALUES (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,NULL,10);

------------------------------------------------------------

Workaround: The following SQL statement finds the number of employees and total salary of each department.

The Case Query: 

WITH employee_details AS

  (SELECT deptno,

          nvl(sal,0)+nvl(comm,0) AS total_salary

          FROM emp)

SELECT 

      count (CASE WHEN ed.deptno = 30 THEN 1 ELSE NULL END) sales_persons,

      sum (CASE WHEN ed.deptno = 30 THEN ed.total_salary ELSE 0 END) total_sal_sales,

      count (CASE WHEN ed.deptno = 20 THEN 1 ELSE NULL END) research_scholars,

      sum (CASE WHEN ed.deptno = 20 THEN ed.total_salary ELSE 0 END) total_sal_research,

      count (CASE WHEN ed.deptno = 10 THEN 1 ELSE NULL END) accountants,

      sum (CASE WHEN ed.deptno = 10 THEN ed.total_salary ELSE 0 END) total_sal_accounting,

      count (CASE WHEN ed.deptno = 40 THEN 1 ELSE NULL END) operations_employees,  

      sum (CASE WHEN ed.deptno = 40 THEN ed.total_salary ELSE 0 END) total_sal_operations

 FROM employee_details ed;

Fig 1: Cost of the case statement query

Fig 2: Output of the case statement query

The PIVOT Query:

SELECT *

  FROM (SELECT deptno, 

               nvl(sal,0)+nvl(comm,0) AS total_salary 

          FROM emp) 

 pivot (count(deptno) , 

        sum(total_salary) total_salary

   FOR deptno IN (10 AS accounting, 20 AS research, 30 AS sales, 40 AS operations)

       );

Fig 3: Cost of the PIVOT query

Fig 4: Output of the PIVOT query

Conclusion: The two executions plans were almost identical and took almost same time to fetch the data. If you want to write compact code then you can prefer PIVOT method - the syntax is very simple.

That's it.

References:

• CASE Expressions
• PIVOT