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!

How to see summarized rows in below scenario

pradeep kateelOct 27 2018 — edited Oct 29 2018

Hi,

I have below table:

create table ft_view (AC_ID NUMBER,SEQ_NO NUMBER,T_CODE NUMBER,
T_ELE VARCHAR2(200),T_GEN VARCHAR2(1),T_INC VARCHAR2(1),T_NO NUMBER ,NET_AMT NUMBER,G_AMT NUMBER,P_P_UNIT NUMBER,TRANSACTION_AMT NUMBER,
N_ID NUMBER,P_AMT NUMBER,G_DR NUMBER,REV_AMT NUMBER,F_NO NUMBER,B_NO NUMBER,I_NO NUMBER,O_REV_ID NUMBER,ORG_R NUMBER,CK_NO NUMBER,
TRANSACTION_NO NUMBER,UNIT VARCHAR2(200),REV_ID NUMBER,F_VIEW NUMBER,REF varchar2(200),REMARK varchar2(200))
;

With below data

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (11235,10589,2151,' 10%','Y','Y',null,29.090909090909,32,32,32,12999,32,32,29.090909090909,10630,86,null,10650,102,null,10618,'MAIN',10650,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (11235,10589,8000,null,'Y','Y',1,2.909090909091,null,2.91,2.91,12999,2.91,null,null,10630,86,null,10650,102,null,10619,'MAIN',10650,1,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (11236,10590,3304,' 10%','Y','Y',null,2.909090909091,3.2,3.2,3.2,12999,3.2,3.2,2.909090909091,10630,86,null,10650,102,null,10620,'MAIN',10650,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (11236,10590,8000,null,'Y','Y',1,0.290909090909,null,0.29,0.29,12999,0.29,null,null,10630,86,null,10650,102,null,10621,'MAIN',10650,1,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (11242,10830,8502,null,'Y','N',null,0,0,0,0,null,0,null,null,null,null,null,null,null,null,10850,'MAIN',null,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14760,14162,2000,' 10%','Y','Y',null,13.636363636364,15,15,15,13710,15,15,13.636363636364,11880,91,null,11900,202,null,14220,'MAIN',11900,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14760,14162,8000,null,'Y','Y',1,1.363636363636,null,1.36,1.36,13710,1.36,null,null,11880,91,null,11900,202,null,14221,'MAIN',11900,1,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14761,14163,2000,' 10%','Y','Y',null,13.636363636364,15,15,15,14211,15,15,13.636363636364,13130,92,null,12401,106,null,14222,'MAIN',12401,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14761,14163,8000,null,'Y','Y',1,1.363636363636,null,1.36,1.36,14211,1.36,null,null,13130,92,null,12401,106,null,14223,'MAIN',12401,1,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14762,14164,2000,' 10%','Y','Y',null,13.636363636364,15,15,15,14212,15,15,13.636363636364,13131,93,null,12402,206,null,14224,'MAIN',12402,1,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14762,14164,8000,null,'Y','Y',1,1.363636363636,null,1.36,1.36,14212,1.36,null,null,13131,93,null,12402,206,null,14225,'MAIN',12402,1,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14763,14165,2000,' 10%','Y','Y',null,27.272727272727,30,30,30,14210,30,30,27.272727272727,17131,94,null,12400,104,null,14226,'MAIN',12400,2,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14763,14165,8000,null,'Y','Y',1,2.727272727273,null,2.73,2.73,14210,2.73,null,null,17131,94,null,12400,104,null,14227,'MAIN',12400,2,'Charged and 10% addded',null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14764,14166,2000,' 10%','Y','Y',null,27.272727272727,30,30,30,16212,30,30,27.272727272727,17130,96,null,15651,101,null,14228,'MAIN',15651,2,null,null);

Insert into FT_VIEW (AC_ID,SEQ_NO,T_CODE,T_ELE,T_GEN,T_INC,T_NO,NET_AMT,G_AMT,P_P_UNIT,TRANSACTION_AMT,N_ID,P_AMT,G_DR,REV_AMT,F_NO,B_NO,I_NO,O_REV_ID,ORG_R,CK_NO,TRANSACTION_NO,UNIT,REV_ID,F_VIEW,REF,REMARK) values (14764,14166,8000,null,'Y','Y',1,2.727272727273,null,2.73,2.73,16212,2.73,null,null,17130,96,null,15651,101,null,14229,'MAIN',15651,2,'Charged and 10% addded',null);

Table data: (Given few columns only)

AC_ID	SEQ_NO	T_CODE	T_ELE	T_GEN	T_INC	T_NO	NET_AMT	G_AMT	P_P_UNIT	TRANSACTION_AMT	N_ID	P_AMT
11235	10589	2151	10%	Y	Y		29.09090909	32	32	32	12999	32
11235	10589	8000		Y	Y	1	2.909090909		2.91	2.91	12999	2.91
11236	10590	3304	10%	Y	Y		2.909090909	3.2	3.2	3.2	12999	3.2
11236	10590	8000		Y	Y	1	0.290909091		0.29	0.29	12999	0.29
11242	10830	8502		Y	N		0	0	0	0		0
14760	14162	2000	10%	Y	Y		13.63636364	15	15	15	13710	15
14760	14162	8000		Y	Y	1	1.363636364		1.36	1.36	13710	1.36
14761	14163	2000	10%	Y	Y		13.63636364	15	15	15	14211	15
14761	14163	8000		Y	Y	1	1.363636364		1.36	1.36	14211	1.36
14762	14164	2000	10%	Y	Y		13.63636364	15	15	15	14212	15
14762	14164	8000		Y	Y	1	1.363636364		1.36	1.36	14212	1.36
14763	14165	2000	10%	Y	Y		27.27272727	30	30	30	14210	30
14763	14165	8000		Y	Y	1	2.727272727		2.73	2.73	14210	2.73
14764	14166	2000	10%	Y	Y		27.27272727	30	30	30	16212	30
14764	14166	8000		Y	Y	1	2.727272727		2.73	2.73	16212	2.73

Result I need:

AC_ID	SEQ_NO	T_CODE	T_ELE	T_GEN	T_INC	T_NO	NET_AMT	G_AMT	P_P_UNIT	TRANSACTION_AMT	N_ID	P_AMT
11235	10589	2151	10%	Y	Y		32	32	32	32	12999	32
11236	10590	3304	10%	Y	Y		3.2	3.2	3.2	3.2	12999	3.2
11242	10830	8502		Y	N		0	0	0	0		0
14760	14162	2000	10%	Y	Y		15	15	15	15	13710	15
14761	14163	2000	10%	Y	Y		15	15	15	15	14211	15
14762	14164	2000	10%	Y	Y		15	15	15	15	14212	15
14763	14165	2000	10%	Y	Y		32.72	30	32.72	32.72	14210	30
14764	14166	2000	10%	Y	Y		30	30	30	30	16212	30

I want to see rows summarized rows for all the AMT columns for AC_ID is more than one. If AC_ID is one then fine, if its more than 2 rows then need to see all the AMT columns as summarized for that particular ac_id. Final result i need to see unique AC_ID with summarized amount.

Please help to get the code.

This post has been answered by Etbin on Oct 28 2018

Jump to Answer

Added on Oct 27 2018

#sql

10 comments

257 views