B4J Question How to aggregate self referencing db records easily?

[Mashiane]

Hi there

I need some source code help please. I need to aggregate some self referencing records. Each record in my database has a parent, but this is a flat structure. The genesis records will have a parentid=0, and their children have parentid=genesisid etc etc

For example,

id=1,wbs=1,parentid=0,startdate=2018-03-10,finishdate=2018-03-31,progress=10,budget=20
id=2,wbs=2,parentid=0,startdate=2018-03-10,finishdate=2018-03-31,progress=10,budget=30
id=3,wbs=3,parentid=0,startdate=2018-01-11,finishdate=2018-03-31,progress=10,budget=100
id=4,wbs=3.1,parentid=3,startdate=2018-03-10,finishdate=2018-03-31,progress=2,budget=200
id=5,wbs=2.2,parentid=2,startdate=2018-02-01,finishdate=2018-03-31,progress=1,budget=500
id=6,wbs=5.1,parentid=5,startdate=2018-02-01,finishdate=2018-03-31,progress=0,budget=400

Now what I want to do is

• Sum: sum the budgets of children per parents and put the sum as the budget amount for parents
• Min & Max: find the minimum and maximum dates for the children and put this for parents.
• Average: find the average progress for children and put on for parents

These are just are a few example calculations.

Now, there can be a lot of parents and a lot of children here in the same db table. I need some simple methodology and perhaps source to do this please.

Thanks a lot!

 

[keirS]

We are almost there, kids 3 & 2 have parent 1, but their total is 17 pounds and not 19 pounds. kid 2 is supposed to be 11 pounds.

Thanks a million for this, I have learned a few important things I didnt know. At some stage I will have to do same in MySQL and MSSQL databases.

You said in your previous post that the childmoney for 1 should be 19 ponds. Provide a grid with the results you expect and I will take a look.

 

[Mashiane]

@keirS, yes that is correct, I will send you a grid asap, however take a look at this post. https://www.b4x.com/android/forum/t...erencing-db-records-easily.90480/#post-571998 in the meantime please.