add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
version()
8.0.30
create table `bills` (
`id` int (11) primary key,
`description` varchar (75),
`amount` float ,
`sortid` float ,
`lvl` int (11),
index (amount, lvl)
);

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');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('8','Top Ups',50,'2','0');
insert into `bills` (`id`, `description`, `amount`, `sortid`, `lvl`) values('9',' Mobilink',50,'2.1','1');

SELECT * from bills
id description amount sortid lvl
1 Utilities 1 0
2 Gas and Electric 1.1 1
3 Gas 50 1.11 2
4 Electric 100 1.12 2
5 Telephone 90 1.13 2
6 Phone 1 30 1.131 3
7 Phone 2 40 1.132 3
8 Top Ups 50 2 0
9 Mobilink 50 2.1 1
SELECT
b.*,
(SELECT
SUM(amount)
FROM bills b2
WHERE b2.lvl > b.lvl
AND SUBSTRING_INDEX(b.sortid, '.', 1) = SUBSTRING_INDEX(b2.sortid, '.', 1)
) lvl_total
FROM bills b
id description amount sortid lvl lvl_total
1 Utilities 1 0 310
2 Gas and Electric 1.1 1 310
3 Gas 50 1.11 2 70
4 Electric 100 1.12 2 70
5 Telephone 90 1.13 2 70
6 Phone 1 30 1.131 3
7 Phone 2 40 1.132 3
8 Top Ups 50 2 0 50
9 Mobilink 50 2.1 1
SELECT
b.*,
SUM(b2.amount) lvl_total
FROM bills b
LEFT JOIN bills b2 ON b2.lvl > b.lvl
AND SUBSTRING_INDEX(b.sortid, '.', 1) = SUBSTRING_INDEX(b2.sortid, '.', 1)
GROUP BY b.id

id description amount sortid lvl lvl_total
1 Utilities 1 0 310
2 Gas and Electric 1.1 1 310
3 Gas 50 1.11 2 70
4 Electric 100 1.12 2 70
5 Telephone 90 1.13 2 70
6 Phone 1 30 1.131 3
7 Phone 2 40 1.132 3
8 Top Ups 50 2 0 50
9 Mobilink 50 2.1 1