Skip to Main Content
  • Questions
  • Need help with PIVOT SQL to calculate totals of pivoted columns

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rotan.

Asked: January 17, 2018 - 12:51 am UTC

Answered by: Chris Saxon - Last updated: April 12, 2019 - 2:20 am UTC

Category: SQL - Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Viewed 1000+ times

You Asked

Hi ,

I need help with getting the totals of each row and column of a table.
I used PIVOT to segregate the data but am not able to figure out about the totals.
Below is the script, current output and required output. Request for help .. thank you..
Oracle Databse - 11.2.0.4.0 - 64bit

------------------------------
CREATE TABLE 
------------------------------

CREATE TABLE TEST_RES_TAB
(
  RESOURCE_ID        NUMBER              NOT NULL,
  RESOURCE_NAME      VARCHAR2(240)       NOT NULL,
  ROLE               VARCHAR2(50),
  TRACK              VARCHAR2(50),
  LOCATION           VARCHAR2(50),
  RESOURCE_TYPE      VARCHAR2(50),
  SITE_TYPE          VARCHAR2(50)
)


------------------------------
INSERT DATA
------------------------------

begin
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990001,'RES_26','PM','RTR','UK','TMP','OffSite',to_date('11-MAY-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2018 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990002,'RES_25','BSA','AMS','UK','TMP','OffSite',to_date('28-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990003,'RES_24','BSA','BI','US','TMP','OnSite',to_date('13-OCT-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-2018 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990004,'RES_23','Manager','RTR','US','FTE','OnSite',to_date('09-SEP-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990005,'RES_22','QA','OPRN','UK','TMP','OffSite',to_date('01-NOV-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990006,'RES_21','BSA','CTO','US','TMP','OnSite',to_date('18-MAY-2014 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('20-JAN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990007,'RES_20','PM','PMO','US','TMP','OnSite',to_date('12-SEP-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('19-MAY-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990008,'RES_19','Developer','CTO','UK','TMP','OnSite',to_date('02-MAR-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('15-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990009,'RES_18','Developer','CTO','UK','TMP','OnSite',to_date('02-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990010,'RES_17','Manager','CTO','US','FTE','OnSite',to_date('30-AUG-2010 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990011,'RES_16','BSA','PTP','US','FTE','OnSite',to_date('29-AUG-2011 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990012,'RES_15','BSA','RTR','US','TMP','OnSite',to_date('26-JAN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('28-JUL-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990013,'RES_14','Developer','BI','US','FTE','OnSite',to_date('14-APR-2014 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990014,'RES_13','Leadership','MGMT','CAN','FTE','OnSite',to_date('12-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990015,'RES_12','Dev Lead','CTO','CAN','FTE','OnSite',to_date('25-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990016,'RES_11','Dev Lead','CTO','CAN','TMP','OnSite',to_date('21-AUG-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990017,'RES_10','PM','PMO','CAN','FTE','OnSite',to_date('06-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990018,'RES_9','DBA','DBA','CAN','TMP','OnSite',to_date('28-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-MAR-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990019,'RES_8','Leadership','MGMT','CAN','FTE','OnSite',to_date('15-APR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990020,'RES_7','Developer','RTR','US','TMP','OnSite',to_date('08-MAR-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('14-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990021,'RES_6','Developer','CTO','UK','TMP','OffSite',to_date('31-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('07-APR-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990034,'RES_5','Dev Lead','AMS','US','TMP','OnSite',to_date('21-SEP-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990035,'RES_4','QA','OPRN','UK','TMP','OffSite',to_date('08-FEB-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990036,'RES_3','Manager','CTO','US','FTE','OnSite',to_date('07-DEC-2009 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990046,'RES_2','Manager','CTO','US','FTE','OnSite',to_date('29-OCT-2012 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990047,'RES_1','Developer','CTO','US','TMP','OnSite',to_date('31-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-MAY-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
commit;
end;

------------------------------
MY QUERY
------------------------------

SELECT *
FROM   (  SELECT resource_type status,
         site_type on_off_site,
         location,
         COUNT (resource_name) res_cnt
    FROM TEST_RES_TAB
GROUP BY resource_type, site_type, location
)
PIVOT (
sum(res_cnt) FOR location IN ('US' US,  'UK' UK, 'CAN' CANADA)
)  

------------------------------
CURRENT OUTPUT
------------------------------

STATUS |    ON_OFF_SITE |        US |    UK  |  CANADA
FTE  |   OnSite  |             6 |  |   4
TMP  |   OnSite  |             7 |    2  |   2
TMP  |   OffSite |                       |    5  |


------------------------------
REQUIRED OUTPUT  -- feel free to remove PIVOT and use DECODE if that will make it simple.
------------------------------
STATUS |ON_OFF_SITE    |US     |UK     |CANADA |Total
FTE     |OnSite         |6      |       |4      |10
TMP     |OnSite         |7      |2      |2      |11
TMP     |OffSite        |       |       |5      |5
Total   |               |13     |7      |6      |26


Any Help is much appreciated.
Thank you.

and we said...

So, you want to generate the totals for:

- resource_type, site_type, location
- resource_type, site_type
- location
- The grand total

You can generate these in your subquery using grouping sets (). Pass to this all the combinations of columns you want subtotals for:

select grouping_id (resource_type, site_type, location) grp,
         resource_type status,
         site_type on_off_site,
         location,
         count(*) c
  from   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  );

GRP   STATUS   ON_OFF_SITE   LOCATION   C    
    0 FTE      OnSite        US            6 
    0 FTE      OnSite        CAN           4 
    0 TMP      OnSite        UK            2 
    0 TMP      OnSite        US            7 
    0 TMP      OnSite        CAN           2 
    0 TMP      OffSite       UK            5 
    1 FTE      OnSite                     10 
    1 TMP      OnSite                     11 
    1 TMP      OffSite                     5 
    6                        CAN           6 
    6                        UK            7 
    6                        US           13 
    7                                     26


The grouping_id function tells you which combination of columns the total is for. Use this to map subtotals to values you want in your columns.

You want the totals for

- Each location to be a row
- Each (resource_type, site_type) to be a column

So map these to a 'TOTAL' value when it has the appropriate grouping_id:

select case 
           when grouping_id (resource_type, site_type, location) in (6, 7) then  
             'TOTAL'
           else
             resource_type 
         end status,
         site_type on_off_site,
         case
           when grouping_id (resource_type, site_type, location) in (1, 7) then
             'TOTAL'
           else
             location
         end location,
         count(*) c
  FROM   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  );

STATUS   ON_OFF_SITE   LOCATION   C    
FTE      OnSite        US            6 
FTE      OnSite        CAN           4 
TMP      OnSite        UK            2 
TMP      OnSite        US            7 
TMP      OnSite        CAN           2 
TMP      OffSite       UK            5 
FTE      OnSite        TOTAL        10 
TMP      OnSite        TOTAL        11 
TMP      OffSite       TOTAL         5 
TOTAL                  CAN           6 
TOTAL                  UK            7 
TOTAL                  US           13 
TOTAL                  TOTAL        26


All you need to do now is add total to you pivot list for location:

with tots as (
  select case 
           when grouping_id (resource_type, site_type, location) in (6, 7) then  
             'TOTAL'
           else
             resource_type 
         end status,
         site_type on_off_site,
         case
           when grouping_id (resource_type, site_type, location) in (1, 7) then
             'TOTAL'
           else
             location
         end location,
         count(*) c
  FROM   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  )
)
  select * from tots
  pivot (
    sum(c) for location IN ('US' US,  'UK' UK, 'CAN' CANADA, 'TOTAL' TOTAL)
  );

STATUS   ON_OFF_SITE   US   UK   CANADA   TOTAL   
FTE      OnSite           6             4      10 
TMP      OnSite           7    2        2      11 
TMP      OffSite               5                5 
TOTAL                    13    7        6      26

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thank You for the in depth Explanation.

January 17, 2018 - 5:49 pm UTC

Reviewer: A reader


This helps.
I was not aware of grouping sets and grouping_id().
Will dig into it and learn.. Thanks again.

Chris Saxon

Followup  

January 17, 2018 - 8:56 pm UTC

Thanks, glad it helped

April 12, 2019 - 2:19 am UTC

Reviewer: Paul Cunningham

An excellent step by step explanation that has really helped me to understand the concept of nested grouping_id sets.
Is there a method that can be used to return zero as the count instead of null in the pivot output where there are no rows present?
Connor McDonald

Followup  

April 12, 2019 - 2:20 am UTC

A simple NVL around the column should suffice

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.