I have this data which is selected from two tables.

Screenshot of data

I want to add sum of amount of sub level to its parent for example level 3 two entries are added to level 2 and sum of all level 2 entries be added to level 1 and finally total is added to level 0.

following is table structure and data

create table `bills` (
    `id` int (11),
    `description` varchar (75),
    `amount` float ,
    `sortid` float ,
    `lvl` int (11)
); 
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('1','Utilities',NULL,'1','0');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('2','  Gas and Electric',NULL,'1.1','1');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('3','     Gas','50','1.11','2');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('4','     Electric','100','1.12','2');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('5','     Telephone','90','1.13','2');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('6','        Phone 1','30','1.131','3');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('7','        Phone 2','40','1.132','3');

CodePudding user response：

There is nothing to do with the recursive CTE, since aggregation is required to calculate the sum over sublevels, which is not allowed in the recursive part of the CTE.

So, you can use subquery or LEFT JOIN the table with self to get the sum of amount values for all rows with lvl values greater than the current one.

Using subquery example

SELECT
    b.*,
    (SELECT 
         SUM(amount) 
     FROM bills b2 
     WHERE b2.lvl > b.lvl) lvl_total
FROM bills b

Using LEFT JOIN example

SELECT
    b.*,
    SUM(b2.amount) lvl_total
FROM bills b
LEFT JOIN bills b2 ON b2.lvl > b.lvl
GROUP BY b.id

Both queries produce the same results.

You need the id column to be the primary key. Also you need to create a covering index index(amount,lvl) to improve query performance.

You can test them on real data to find the best one. I suppose that subquery would the best.

db<>fiddle here

CodePudding user response：

I summed the amount by lvl and then ran a running sum on the result.

select   lvl
        ,sum(amount) over(order by lvl desc rows unbounded preceding) as running_sum
from    (
         select   lvl
                 ,sum(amount) as amount
         from     bills
         group by lvl
         ) bills

Fiddle