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.