Home » SQL & PL/SQL » SQL & PL/SQL » grouping in matrix (12c)
grouping in matrix [message #666650] Sun, 19 November 2017 04:13 Go to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
Hello experts,

i have created a pivot query in oracle , but how to get the totals by certain value or one field inv_account row wise and column wise like a group.


create table invoice_data(inv_comp varchar2(12),inv_account varchar2(12) ,inv_buis_unit varchar2(12),inv_amt number);

insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C02' ,'301'  ,'BWD001',10 );
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C03' ,'301'  ,'CHM001',10 );       
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C04' ,'301'  ,'SER001',10 );
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C05' ,'301'  ,'MNT001',10 );


insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C02' ,'302'  ,'BWD001',10 );
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C03' ,'302'  ,'CHM001',10 ) ;      
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C04' ,'302'  ,'SER001',10 );
insert into invoice_datA(inv_comp ,inv_account  ,inv_buis_unit,inv_amt ) values ('C05' ,'302'  ,'MNT001',10 );


SELECT * FROM INVOICE_DATA ORDER BY 2


SELECT INV_ACCOUNT,INV_BUIS_UNIT,SUM(DECODE(INV_COMP,'C02',INV_AMT,0)) C02_TOTAL, 
SUM(DECODE(INV_COMP,'C03',INV_AMT,0)) C03_TOTAL, 
SUM(DECODE(INV_COMP,'C04',INV_AMT,0)) C04_TOTAL, 
SUM(DECODE(INV_COMP,'C05',INV_AMT,0)) C05_TOTAL
FROM INVOICE_DATA
GROUP BY INV_ACCOUNT,INV_BUIS_UNIT
ORDER BY 1


i am getting output like below.


301	BWD001	10	0	0	0
301	CHM001	0	10	0	0
301	MNT001	0	0	0	10
301	SER001	0	0	10	0
302	BWD001	10	0	0	0
302	CHM001	0	10	0	0
302	MNT001	0	0	0	10
302	SER001	0	0	10	0

but i want total by 301 and 302 by row.



301	BWD001	10	0	0	0
301	CHM001	0	10	0	0
301	MNT001	0	0	0	10
301	SER001	0	0	10	0
                10      0       10      10
302	BWD001	10	0	0	0
302	CHM001	0	10	0	0
302	MNT001	0	0	0	10
302	SER001	0	0	10	0
                10      0       10      10








Re: grouping in matrix [message #666651 is a reply to message #666650] Sun, 19 November 2017 07:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
SELECT  INV_ACCOUNT,
        INV_BUIS_UNIT,
        SUM(DECODE(INV_COMP,'C02',INV_AMT,0)) C02_TOTAL,
        SUM(DECODE(INV_COMP,'C03',INV_AMT,0)) C03_TOTAL,
        SUM(DECODE(INV_COMP,'C04',INV_AMT,0)) C04_TOTAL,
        SUM(DECODE(INV_COMP,'C05',INV_AMT,0)) C05_TOTAL
  FROM INVOICE_DATA
  GROUP BY GROUPING SETS((INV_ACCOUNT),(INV_ACCOUNT,INV_BUIS_UNIT))
  ORDER BY INV_ACCOUNT,
           GROUPING(INV_BUIS_UNIT)
/

INV_ACCOUNT  INV_BUIS_UNI  C02_TOTAL  C03_TOTAL  C04_TOTAL  C05_TOTAL
------------ ------------ ---------- ---------- ---------- ----------
301          BWD001               10          0          0          0
301          CHM001                0         10          0          0
301          MNT001                0          0          0         10
301          SER001                0          0         10          0
301                               10         10         10         10
302          BWD001               10          0          0          0
302          CHM001                0         10          0          0
302          MNT001                0          0          0         10
302          SER001                0          0         10          0
302                               10         10         10         10

10 rows selected.

SQL> 

SY.
Re: grouping in matrix [message #666674 is a reply to message #666651] Mon, 20 November 2017 04:08 Go to previous messageGo to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
thanks solomon,

On test solution it is working perfectly , but when i applied the same logic to my actual query which is having a function in main query, the results are not desirable.


SELECT c.description Account_Desc,
        Authorize_Comb_Finance_API.Get_Code_Part_Text (a.company,
                                                        'CODEC',
                                                        a.code_c)
            Bu_Desc,
         SUM (DECODE (a.company, 'C02', (a.curr_amount) * -1, 0)) C02,
         SUM (DECODE (a.company, 'C03', (a.curr_amount) * -1, 0)) C03,
         SUM (DECODE (a.company, 'C04', (a.curr_amount) * -1, 0)) C04,
         SUM (DECODE (a.company, 'C05', (a.curr_amount) * -1, 0)) C05
    FROM inv_accounting_row2 a, outgoing_invoice2 b, account c
   WHERE     a.invoice_id = b.invoice_id
         AND A.CODE_A = C.ACCOUNT
         AND B.COMPANY = C.COMPANY
         AND CODE_A IN ('30101', '30201')
         AND b.objstate <> 'Cancelled'
         AND TRUNC (b.INVOICE_DATE) BETWEEN TRUNC (SYSDATE, 'YYYY')
                                        AND TRUNC (SYSDATE) GROUP BY      
         GROUPING SETS((C.DESCRIPTION),(Authorize_Comb_Finance_API.Get_Code_Part_Text (a.company,
                                                        'CODEC',
                                                        a.code_c),a.company,a.code_c))
ORDER BY c.description ,  grouping (Authorize_Comb_Finance_API.Get_Code_Part_Text (a.company,
                                                        'CODEC',
                                                        a.code_c))

--ouput i am getting is

ACCOUNT_DESC  BU_DESC           C02             C03             C04             C05
Sales - Intercompany		   113,575.870	2,228,034.780       7,250.000	0.000
Sales -Third party		19,187,553.829	4,397,516.310   4,826,214.050	0.000
	Chemical Lab	                 0.000	   59,408.000           0.000	0.000
	Pre-commission cleaning          0.000	        0.000	  552,600.000	0.000
	Equipment Service -              0.000	        0.000	  206,100.000	0.000
	Pre-commission cleaning  3,334,516.250	        0.000	        0.000	0.000
	




CM: added missing end code tag

[Updated on: Mon, 20 November 2017 04:13] by Moderator

Report message to a moderator

Re: grouping in matrix [message #666683 is a reply to message #666674] Mon, 20 November 2017 05:02 Go to previous message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
Look at grouping sets in my example:

GROUP BY GROUPING SETS((INV_ACCOUNT),(INV_ACCOUNT,INV_BUIS_UNIT))

And compare it to your:


GROUP BY GROUPING SETS((C.DESCRIPTION),(Authorize_Comb_Finance_API.Get_Code_Part_Text (a.company,'CODEC',a.code_c),a.company,a.code_c))

SY.
Previous Topic: Query Analysis
Next Topic: how can get Three consecutive days
Goto Forum:
  


Current Time: Fri Dec 15 04:13:30 CST 2017

Total time taken to generate the page: 0.02463 seconds