Sum of child values in parent

Question

0.00/5 (No votes)

See more:

I have the following table in my database:

ID      IsLeaf     Description     Parent_ID     value
1--------0 ------------11--------------0-------------0
2--------0 ------------22--------------0-------------0
3--------1 ------------110001----------1-------------100
4--------0 ------------2202------------2-------------0
5--------1 ------------22020001--------4-------------159
6--------1 ------------110002----------1-------------40

I want the sum of the children to be in its parent, and when the parent is also a child of another parent, then the sum of the parents should be in its parent.

When I retrieve data I want to get it like this in a DataTable. I use Winforms and C#.

ID      IsLeaf     Description     Parent_ID     value
1--------0 ------------11--------------0-------------140
2--------0 ------------22--------------0-------------159
3--------1 ------------110001----------1-------------100
4--------0 ------------2202------------2-------------159
5--------1 ------------22020001--------4-------------159
6--------1 ------------110002----------1-------------40

this some Notes:
1-IsLeaf column:
if 1 "true" that's mean the Record can take value and can not make child of it.
if 0 "false" that's mean the Record can not take value and can make child of it.
but in dataTable the Parent Record Has The Sum Of Its Child

2-If the solution Need any Edit In columns (Add or Remove) thats not problem.

i need solution

I appreciate any replies or advice. Thank you.

Posted 12-Apr-11 11:27am

mAdelm

Updated 13-Apr-11 13:19pm

v3

Add a Solution

Comments

Pong D. Panda 12-Apr-11 23:12pm

Do you want to do it on your Database (using functions) or during DataTable rendering on c#?

Sandeep Mewara 13-Apr-11 6:23am

Either you do it in query or in Business Logic layer. Where you want to and have tried?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ali Al Omairi(Abu AlHassan) · Answer 1 · 2011-04-13T23:15:00

lets try something

C#

void iteratparents(){
  foreach (DataRow parentrecord in mydatatable.Select("Parent_ID=0")
    setparentvalue(parentrecord);
}

void setparetrecord(DataRow parentrecord){
  if (!((bool) parentrecord["IsLeaf"])) {
    int sum = 0;
    DataRow[] children = mydatatable.Select("Parent_ID=" + (string) parentrecord["ID"] );
    foreach (DataRow childrecord in children){
      setparetrecord(childrecord);
      sum += (int) childrecord["value"];
    }

    parentrecord["value"] = sum;
  }
}

Good Luck;

100 :rose:

Sum of child values in parent

1 solution

Solution 2

Add your solution here

Preview 0

Sum of child values in parent

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us