MySQL sum of column group by question

Question

I have the results of a select that I am joining on that I need to get a slightly complex sum of a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7 
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated

Answer 1

SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result 
FROM test as t1 
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a 
GROUP BY id,name ORDER BY id