All,
I am running 11.2.0.1 on OEL 5 and I am struggling with figuring out how to do a row sum in a pivot query.
Here is the data
CREATE TABLE "KZ_REG"
( "ID" NUMBER,
"AMOUNT" NUMBER,
"KZ_NAME_ID" NUMBER,
"REGION_ID" NUMBER,
CONSTRAINT "KZ_REG_PK" PRIMARY KEY ("ID") ENABLE
)
/
CREATE TABLE "KZ_NAME_LOOKUP"
( "KZ_NAME_ID" NUMBER NOT NULL ENABLE,
"KZ_NAME" VARCHAR2(45) NOT NULL ENABLE,
PRIMARY KEY ("KZ_NAME_ID") ENABLE
)
/
CREATE TABLE "REGION_LOOKUP"
( "REGION_ID" NUMBER NOT NULL ENABLE,
"REGION" VARCHAR2(5) NOT NULL ENABLE,
PRIMARY KEY ("REGION_ID") ENABLE
)
/
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(1,'EMEA');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(2,'LAD');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(3,'APAC');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(4,'NAS');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(5,'JAPAN');
/
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(1,'KZ_1');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(2,'KZ_2');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(3,'KZ_3');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(4,'KZ_4');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(5,'KZ_5');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(6,'KZ_6');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(7,'KZ_7');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(8,'KZ_8');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(9,'KZ_9');
INSERT INTO KZ_NAME_LOOKUP (KZ_NAME_ID, KZ_NAME VALUES(10,'KZ_10');
/
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(41,5,2,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(42,7,2,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(43,2,2,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(44,13,2,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(45,0,2,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(46,6,4,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(47,7,4,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(48,2,4,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(49,13,4,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(50,0,4,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(51,20,6,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(52,31,6,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(53,9,6,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(54,19,6,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(55,0,6,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(56,24,1,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(57,39,1,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(58,8,1,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(59,26,1,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(60,0,1,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(61,24,3,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(62,37,3,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(63,8,3,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(64,29,3,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(65,1,3,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(66,26,7,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(67,41,7,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(68,10,7,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(69,23,7,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(70,0,7,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(71,26,5,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(72,40,5,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(73,10,5,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(74,31,5,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(75,0,5,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(76,29,8,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(77,42,8,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(78,10,8,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(79,35,8,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(80,0,8,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(81,29,9,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(82,49,9,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(83,17,9,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(84,43,9,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(85,2,9,5);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(86,47,10,3);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(87,67,10,1);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(88,25,10,2);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(89,36,10,4);
INSERT INTO KZ_REG (ID, AMOUNT, KZ_NAME_ID, REGION_ID VALUES(90,1,10,5);
/
Here is my query:
SELECT kn,
EMEA,
LAD,
APAC,
NAS,
JAPAN
FROM
(SELECT KZ.KZ_NAME AS kn,
RL.REGION AS re,
KR.AMOUNT AS am
FROM REGION_LOOKUP RL,
KZ_NAME_LOOKUP KZ,
KZ_REG KR
WHERE rl.region_id = kr.region_id
AND kz.kz_name_id = kr.kz_name_id
) pivot (SUM(AM) FOR re IN ('EMEA' AS EMEA, 'LAD' AS LAD, 'APAC' AS APAC, 'NAS' AS NAS, 'JAPAN' AS JAPAN))
ORDER BY kn
which returns the following, I changed the KN values as the data is sort of sensitve:
KN EMEA LAD APAC NAS JAPAN
KZ_XX 37 8 24 29 1
KZ_XX 31 9 20 19 0
KZ_XX 7 2 5 13 0
KZ_XX 41 10 26 23 0
KZ_XX 42 10 29 35 0
KZ_XX 7 2 6 13 0
KZ_XX 40 10 26 31 0
KZ_XX 49 17 29 43 2
KZ_XX 67 25 47 36 1
KZ_XX 39 8 24 26 0
But what I'd like to get is something like this, note tha extra line and column Total.
KN EMEA LAD APAC NAS JAPAN Total
KZ_XX 37 8 24 29 1 99
KZ_XX 31 9 20 19 0 79
KZ_XX 7 2 5 13 0 27
KZ_XX 41 10 26 23 0 100
KZ_XX 42 10 29 35 0 116
KZ_XX 7 2 6 13 0 28
KZ_XX 40 10 26 31 0 107
KZ_XX 49 17 29 43 2 140
KZ_XX 67 25 47 36 1 176
KZ_XX 39 8 24 26 0 97
Total 360 101 236 268 4 969
Any idea on how I could accomplish this?
Thanks
Andy