3 different ways to write a SQL to get an aggregate output report

There are more than one way of doing things and it is true in Oracle SQL too. Take a look at following example where we want to generate a report with SUCCESS and ERROR count in a single line for ACTION=’Payment’.

SQL> select * from MAD_MESSAGES;

ACTION               STATUS               CREATE_TI

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

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              SUCCESS              23-OCT-13

Payment              ERROR                23-OCT-13

Payment              ERROR                23-OCT-13

Payment              ERROR                23-OCT-13

Payment              ERROR                23-OCT-13

Payment              ERROR                23-OCT-13

Reciept              SUCCESS              23-OCT-13

26 rows selected.

SQL> select action,status,count(*) from MAD_MESSAGES group by action,status;

ACTION               STATUS                 COUNT(*)

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

Payment              ERROR                         5

Payment              SUCCESS                      20

Reciept              SUCCESS                       1

First way with inline views

select a.SUCCESS_COUNT,b.ERROR_COUNT, ((b.ERROR_COUNT)/decode(a.SUCCESS_COUNT,0,1,a.SUCCESS_COUNT))*100 ERR_PCT from

(SELECT count(*) SUCCESS_COUNT FROM MAD_MESSAGES where STATUS='SUCCESS' and ACTION='Payment' and CREATE_TIME > (sysdate-1)) a,

(SELECT count(*) ERROR_COUNT FROM MAD_MESSAGES where STATUS='ERROR' and  ACTION='Payment' and CREATE_TIME > (sysdate-1)) b

SUCCESS_COUNT ERROR_COUNT    ERR_PCT

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

           20           5         25

Plan

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

| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |              |     1 |    26 |     6   (0)| 00:00:01 |

|   1 |  NESTED LOOPS        |              |     1 |    26 |     6   (0)| 00:00:01 |

|   2 |   VIEW               |              |     1 |    13 |     3   (0)| 00:00:01 |

|   3 |    SORT AGGREGATE    |              |     1 |    24 |            |          |

|*  4 |     TABLE ACCESS FULL| MAD_MESSAGES |    20 |   480 |     3   (0)| 00:00:01 |

|   5 |   VIEW               |              |     1 |    13 |     3   (0)| 00:00:01 |

|   6 |    SORT AGGREGATE    |              |     1 |    24 |            |          |

|*  7 |     TABLE ACCESS FULL| MAD_MESSAGES |     5 |   120 |     3   (0)| 00:00:01 |

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

You could see that it is doing Full table scan 2 times.

Second way with CASE statement in select clause

select SUCCESS_COUNT,ERROR_COUNT,((ERROR_COUNT)/decode(SUCCESS_COUNT,0,1,SUCCESS_COUNT))*100 ERR_PCT

FROM

(

SELECT

count(case when STATUS='SUCCESS' THEN 1

                  else NULL

            end) SUCCESS_COUNT,

count(case when STATUS='ERROR' THEN 1

                  else NULL

            end) ERROR_COUNT

FROM MAD_MESSAGES where ACTION='Payment' and CREATE_TIME > (sysdate-1)

);

SUCCESS_COUNT ERROR_COUNT    ERR_PCT

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

           20           5         25

Plan

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |              |     1 |    26 |     3   (0)| 00:00:01 |

|   1 |  VIEW               |              |     1 |    26 |     3   (0)| 00:00:01 |

|   2 |   SORT AGGREGATE    |              |     1 |    24 |            |          |

|*  3 |    TABLE ACCESS FULL| MAD_MESSAGES |    25 |   600 |     3   (0)| 00:00:01 |

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

It is doing only one Full table scan

Third Way using PIVOT function

SQL> select * from MAD_MESSAGES pivot( count(*) for (status) in ('SUCCESS' AS SUC,'ERROR' AS ERR) ) where ACTION='Payment';

ACTION                      SUC        ERR

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

Payment                      20          5

PLAN

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |              |     1 |    24 |     4  (25)| 00:00:01 |

|   1 |  SORT GROUP BY PIVOT|              |     1 |    24 |     4  (25)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL | MAD_MESSAGES |    25 |   600 |     3   (0)| 00:00:01 |

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

Pivot function is doing only one full table scan.

Please note that you will receive ORA-00933: SQL command not properly ended in 10g as pivot function is not supported