How to obtain the aggregate value of items in subfolders as a column

Question

I'm using MySQL.

FOLDER TABLE

id	parent_folder_id
1	null
2	1
3	1
4	2
5	4
6	4
7	2
8	2

AUDIO_FILE_TABLE

id	parent_folder_id	duration
1	1	10
2	1	20
3	4	20
4	3	30
5	4	10
6	4	20
7	2	20
8	2	20

I would like to have an output to get the sum of duration (include all subfolders), but not sure how to do it. Folder 1 have subfolders [2, 3] at 1st level and then [4, 7, 8] at 2nd level, and then [5, 6] at 3rd level. So all folders are [1, 2, 3, 4, 5, 6, 7, 8] which include all the audios, giving the sum 10 + 20 + 20 + 30 + 10 + 20 + 20 + 20 = 150.

Folder 2 have subfolders [2] at 1st level and then [4, 7, 8] at 2nd level, and then [5, 6] at 3rd level. So all folders are [2, 4, 5, 6, 7, 8] which include audios [3, 5, 6, 7, 8], giving the sum 20 + 10 + 20 + 20 + 20 = 90.

Folder 3 have no subfolder. So all folders are [3] which include audios [4], giving the sum 30.

OUTPUT

id	parent_folder_id	total_duration
1	1	150
2	1	90
3	4	30

Answer 1

You can use a recursive cte:

with recursive cte(id, id1) as (
   select f.id, f.id from folders f
   union all
   select c.id, f.id from cte c join folders f on f.parent_folder_id = c.id1
)
select k.*, f.parent_folder_id from 
   (select c.id, sum(a.duration) from cte c join audio_files a on c.id1 = a.parent_folder_id group by c.id) k 
join audio_files f on f.id = k.id