Recursive count sql

I tried this on our postgres db (Version 8.4.22), since the fiddle was a bit slow for my taste. But the SQL can be pasted in there and it works for postgres.

Still here is the fiddle demo take like 20 sec first time but then is faster.

Here's what produces the calculated results for me. (I didn't format it according to your requirements, because in my mind the main excercise was the calculation.) This assumes your table is called activity:

with recursive rekmatriks as(
    select id, activity, pay, parent, id::text as matriks, 0 as lev
        from activity
        where parent is null
    union all
    select activity.id, activity.activity, activity.pay, activity.parent,
           rekmatriks.matriks || '-' || activity.id::text as matriks,
           rekmatriks.lev+1 as lev
        from activity inner join rekmatriks on activity.parent = rekmatriks.id
)
, reksum as (
    select id, activity, pay, parent, matriks, lev, coalesce(pay,0) as subsum
        from rekmatriks
        where not exists(select id from rekmatriks rmi where rmi.parent=rekmatriks.id)
    union all
    select rekmatriks.*, reksum.subsum+coalesce(rekmatriks.pay, 0) as subsum
        from rekmatriks inner join reksum on rekmatriks.id = reksum.parent)

select id, activity, pay, parent, matriks, sum(subsum) as amount, lev
    from reksum
group by id, activity, pay, parent, matriks, lev
order by id

As a bonus, this delivers the nesting depth of an id. 0 for a parent, 1 for first sublevel etc. This uses two recursive WITH queries to achieve what you want. The calculated value you need is in the amount column.

The first one (rekmatriks) processes the IDs in the table top to bottom, starting with any ids that have a parent of NULL. The recursive part simply takes the parent id and adds it's own id to it, to achieve your matriks tree representation field.

The second one (reksum) works bottom to top and starts with all rows that have no child elements. The recursive part of this query selects a parent row for each child row selected in the non-recursive part, and computes the sum of pay and subsum for each line. This produces multiple rows per id, since one parent can have multiple children.

All that's left now is the final select statement. It uses GROUP BY and SUM to aggregate the multiple possible child sum values into one row.

This does work for your particular example. It may fail if there's different cases not shown in the sample data, for example, if an item that has children carries a value that needs to be added.