Oracle Sum subtotal Query-CodePudding

please help for this requirement is join sum of subtotal.

I have a result table like this. Picture Table

CID     | STORECODE | STORENAME | CONTRACTNO | SUBTOTAL
---------------------------------------------------------
222     | SCI-SCG5  | S2        | 111        | 657,534.20
221     | SCI-SCG5  | S2        | 110        | 700,000

how to get result like this:

CID     | STORECODE | STORENAME | CONTRACTNO | SUBTOTAL
----------------------------------------------------------
222,221 | SCI-SCG5  | S2        | 111,110    | 1.357.534,20

STORECODE | STORENAME | SUBTOTAL
--------------------------------
SCI-SCG5  | S2        | 1.357.534,20

CodePudding user response：

You can use listagg to aggregate strings within groups in oracle:

select listagg(CID,', ') within group( order by CID desc) as CID,
storecode,
storename,
listagg(contractno,', ') within group( order by contractno desc) as CID,
sum(subtotal) as subtotal
from yourtable 
group by storecode, storename;

Fiddle

CodePudding user response：

Aggregations of different kinds:

SQL> with test (cid, storecode, storename, contractno, subtotal) as
  2    (select 222, 'SCI-SCG5', 'S2', 111, 657534.20 from dual union all
  3     select 221, 'SCI-SCG5', 'S2', 110, 700000    from dual
  4    )
  5  select
  6    listagg(cid, ',') within group (order by cid) cid,
  7    storecode,
  8    storename,
  9    listagg(contractno, ',') within group (order by contractno) contractno,
 10    sum(subtotal) subtotal
 11  from test
 12  group by storecode, storename;

CID             STORECOD ST CONTRACTNO             SUBTOTAL
--------------- -------- -- -------------------- ----------
221,222         SCI-SCG5 S2 110,111               1357534,2

SQL>
SQL> with test (cid, storecode, storename, contractno, subtotal) as
  2    (select 222, 'SCI-SCG5', 'S2', 111, 657534.20 from dual union all
  3     select 221, 'SCI-SCG5', 'S2', 110, 700000    from dual
  4    )
  5  select storecode, storename, sum(subtotal) subtotal
  6  from test
  7  group by storecode, storename;

STORECOD ST   SUBTOTAL
-------- -- ----------
SCI-SCG5 S2  1357534,2

SQL>