Recursive sum in query

Go To


I have a table with data stored hierarchicaly using adjacency list like in the following example

id     account parent
1      root    null
2      a1      1
3      b1      1
4      a2      2
5      a3      4
6      a4      2
7      b2      3

And a table where I keep values for these accounts

id_account    value
2             10
2             10
4             20
5             30
6             10 

I made a function which returns all child account given a parent account:

function getChildrenAccount($parent_id)
    $query = "SELECT id, account FROM accounts WHERE parent='{$parent_id}' ORDER BY account";
    $result = mysql_query($query) or die(mysql_error());
    return $r;

What I'm trying to do is a function which returns not only children accounts but also the sum of all values including children of every result. For example


Would return an array with the following syntax

array(1) { 
  [0]=> array(3) { 
     ["id"]=> 5
     ["account"]=> "a3"
     ["sum"]=> 50 //a2 + a3


array(2) { 
  [0]=> array(3) { 
     ["id"]=> 4
     ["account"]=> "a2"
     ["sum"]=> 70 //a1 + a2 + a3
  [1]=> array(3) { 
     ["id"]=> 6
     ["account"]=> "a4"
     ["sum"]=> 30 //a1 + a4

I think I have to use some kind of recursion in my while statement but I'm a little bit confused. Can you help me please?


2012-04-04 03:55
by Jorge Zapata


I found a function which gets results as needed without recursive queries.

$nodeList = array();
$tree     = array();
$query = mysql_query("SELECT A.guid,, A.parent_guid, SUM( S.value_num ) /100 AS suma FROM accounts AS A
    LEFT JOIN splits AS S ON S.account_guid = A.guid GROUP BY A.guid ORDER BY");
while($row = mysql_fetch_assoc($query))
  $nodeList[$row['guid']] = array_merge($row, array('children' => array()));

foreach ($nodeList as $nodeId => &$node) {
  if (!$node['parent_guid'] || !array_key_exists($node['parent_guid'], $nodeList))
    $tree[] = &$node;
    $nodeList[$node['parent_guid']]['children'][] = &$node;
2012-04-10 02:16
by Jorge Zapata


function getChildrenAccount($accountID){

   $query = ' SELECT id,account,sum(value) as `SUM`
    FROM accounts,accountsValues
    WHERE = accountsValues.id_accounts
    AND id = $accountID 
    OR id IN (SELECT id FROM accounts where parent = $accountID) ';
2012-04-04 06:01
by amd
I assume your code only works with one level of nesting but I need something that works for more than one level - Jorge Zapata 2012-04-04 17:04


You need to iterate over your result, then for each id call getChildrenAccount for its parent id.

2012-04-04 17:25
by Aerik
What about the sum? I don't want all id's for nested children but I do want the sum of their values - Jorge Zapata 2012-04-04 17:39
Well... honestly I don't quite understand your example (I don't understand the actual sum calculation - I get the generic problem, "give me all the children's values N levels down" but not the specific example). If you want your array to only show the immediate children, but the sums of those immediate children to include the values of all descendents, then I'd suggest two functions - getChildrenAccount (which builds the arrays) and getDescendentsSums (which recurses all the way down and just gets the sum values) - Aerik 2012-04-04 17:44
Yep as you mentioned I want to show only immediate children but sum of all descendents. I'll try to code your two function approac - Jorge Zapata 2012-04-04 18:16