cumulative sum using join for more than one column

Question

I have 2 tables cat_test and cat_desc

create table cat_test(id varchar2(5),day1_balance number,day2_balance number)

insert into cat_test values(5001,1,34);
insert into cat_test values(5002,1821,34344);
insert into cat_test values(5003,1,34);
insert into cat_test values(5004,1221,3444);


create table cat_desc(cat  varchar2(5),descr varchar2(20))

insert into cat_desc values(5001,'interest1');
insert into cat_desc values(null,'interest');
insert into cat_desc values(5002,'expense1');
insert into cat_desc values(5003,'expense2');
insert into cat_desc values(null,'expense');
insert into cat_desc values(5004,'sales');

select * from cat_test

ID    DAY1_BALANCE DAY2_BALANCE
----- ------------ ------------
5001             1           34
5002          1821        34344
5003             1           34
5004          1221         3444

select * from cat_desc

CAT   DESCR               
----- --------------------
5001  interest1           
      interest            
5002  expense1            
5003  expense2            
      expense             
5004  sales               

i want output as

CAT    DAY1_BALANCE   DAY2_BALANCE     
-----  ------------   -------------
5001        1           34    
            1           34               
5002     1821        34344       
5003        1           34      
         1822        34378  
5004     1221         3444  

however, I can manage to join 2 tables as below using with query

with qur
as
(select a.cat mycat,
(select day1_balance from cat_test b
where a.cat=b.id) myday1,
(select day2_balance from cat_test b
where a.cat=b.id) myday2
from cat_desc a)
select * from qur

MYCAT     MYDAY1     MYDAY2
----- ---------- ----------
5001           1         34

5002        1821      34344
5003           1         34

5004        1221       3444

6 rows selected.

I want to add cumulatively sum balance where ever the cat column is null, please help me to proceed with this further

also, help to join this table in any other way. I want the data order should not change in table2(cat_desc)

Answer 1

You can try to use CTE make the result set for JOIN two tables, then use a subquery JOIN by concat ... like in select to get total. then UNION ALL

create table cat_test(id varchar2(5),day1_balance number,day2_balance number);

insert into cat_test values(5001,1,34);
insert into cat_test values(5002,1821,34344);
insert into cat_test values(5003,1,34);
insert into cat_test values(5004,1221,3444);

create table cat_desc(cat  varchar2(5),descr varchar2(20));

insert into cat_desc values(5001,'interest1');
insert into cat_desc values(null,'interest');
insert into cat_desc values(5002,'expense1');
insert into cat_desc values(5003,'expense2');
insert into cat_desc values(null,'expense');
insert into cat_desc values(5004,'sales');

Query 1:

WITH CTE AS (
SELECT *
FROM cat_test ct RIGHT JOIN cat_desc cd
ON ct.id = cd.cat
)
SELECT CAT,day1_balance, day2_balance FROM 
(
  SELECT DESCR,day1_balance, day2_balance,cat
  FROM CTE
  WHERE cat is not null
  UNION ALL
  SELECT DESCR,(SELECT SUM(day1_balance) 
                FROM CTE t2 
                WHERE t2.DESCR like ('%' || t1.DESCR || '%'))
               ,(SELECT SUM(day2_balance) 
                FROM CTE t2 
                WHERE t2.DESCR like ('%' || t1.DESCR || '%')),
                cat
  FROM CTE t1 
  where cat is null
) t1
ORDER BY DESCR desc 

Results:

|    CAT | DAY1_BALANCE | DAY2_BALANCE |
|--------|--------------|--------------|
|   5004 |         1221 |         3444 |
|   5001 |            1 |           34 |
| (null) |            1 |           34 |
|   5003 |            1 |           34 |
|   5002 |         1821 |        34344 |
| (null) |         1822 |        34378 |

Answer 2

No, it cannot be done.

You database model is incomplete. You are assuming the rows of a table have an intrinsic ordering: they do not. Oracle is free to re-order the rows of a table at any moment.

Therefore, you cannot assume the row (null,'interest') is the "second row" of the table, and that it goes right after the "first row" -- no such a thing. In simple English, a database table is not an Excel sheet.

You'll need to create some kind of relationship between the tables (a foreign key) to record which rows from cat_desc belong to which rows from cat_test.

Until you correct your database model, it's not possible to produce the query you are looking for.