SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Row sum in PIVOT query

ATael-OracleJun 28 2010 — edited Jun 29 2010

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

gimbal2

check the logs; perhaps you are getting validation errors?

833902

I tried checking this but could not make out. It seems below link is releveant for me. My bean is request scoped.

http://community.jboss.org/message/517697

833902

If any one has knowledge about this they can give more details.

gimbal2

Answer

try what the linked article says: make your bean session scoped.

NOTE: when working with Ajax I tend to make the backing bean session scoped to have less issues with partial updates and such. If you use JSF 2.0 you could make it view scoped to get the same result.

Marked as Answer by 833902 · Sep 27 2020

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 27 2010

Added on Jun 28 2010

8 comments

50,650 views

SQL & PL/SQL

Row sum in PIVOT query

Comments

Post Details