Home » SQL & PL/SQL » SQL & PL/SQL » grouping in matrix (12c)
| grouping in matrix [message #666650] |
Sun, 19 November 2017 04:13  |
 |
m.abdulhaq
Messages: 254
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  |
Solomon Yakobson
Messages: 3273
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 |
|
m.abdulhaq
Messages: 254
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 |
Solomon Yakobson
Messages: 3273
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.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 18:11:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
