Recursive sql count
I have a table like this:
id activity pay parent
1 pay all - null
2 pay tax 10 $ 1
3 pay water bills - 1
4 fix house - null
5 fix roof 1 $ 4
6 pay drinking water 1 $ 3
I want to get a table like this:
id activity pay parent matriks
1 pay all {11 $} null 1 (pay tax + pay water bills)
2 pay tax 10 $ 1 1-2
3 pay water bills {1 $} 1 1-3 (pay drinking water)
4 fix house {1 $} null 4 (fix roof)
5 fix roof 1 $ 4 4-5
6 pay drinking water 1 $ 3 1-3-6
Counting from child to parent: The problem is that water bills are not counted in drinking water, all expenses are paid if labor tax or paid water has no wages.
source to share
I tried this on our postgres db (version 8.4.22) as the fiddle was a little slow for my taste. But the SQL can be inserted in there and it works for postgres.
Still here the fiddle demo takes like 20 seconds, but then faster.
This is what gives the calculated results for me. (I didn't format it to suit your requirements, because in my opinion the main exercise was calculation.) This assumes that 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 provides the nesting depth of the identifier. 0 for the parent, 1 for the first sublevel, etc. This uses two recursive WITH queries to achieve what you want. The calculated value is needed in the column amount
.
The first ( rekmatriks
) processes the ids in the table from top to bottom, starting with any ids that have a parent NULL
. The recursive part just takes the parent id and adds its own id to it to get your matriks tree view field.
The second ( reksum
) works from bottom to top and starts on all lines that have no children. The recursive part of this query selects the parent row for each child row selected in the non-recursive part and calculates the sum pay
and subsum
for each row. This creates multiple lines per id, since one parent can have multiple children.
All that's left now is the final select statement. He uses GROUP BY
and SUM
to aggregate multiple subsidiaries amounts possible values in one line.
This works for your specific example. This can be unfortunate if different cases are not displayed in the data selection, for example, if an element with children carries a value that needs to be added.
source to share