MySQL Group By Lowest Common Parent ID
mysql
Solution
If you can define a limit to the number of levels in your hierarcy, you can extend this solution to an arbitrary number of levels:
select
coalesce(t5.ID,t4.ID,t3.ID,t2.ID,t1.ID) as Root,
sum(t1.Amount) as Amount
from Table1 t1
left join Table1 t2 on t1.Parent_ID = t2.ID
left join Table1 t3 on t2.Parent_ID = t3.ID
left join Table1 t4 on t3.Parent_ID = t4.ID
left join Table1 t5 on t4.Parent_ID = t5.ID
group by Root
Just add more `left join` as needed and add them to the list of columns in the `coalesce`.
Demo: http://www.sqlfiddle.com/#!2/b7a79/17
Problem
I have a single table that contains an `ID`, `Amount`, and `Parent_ID`. (It is a little more complex in reality, but this is all the important columns for this example). Basically what i want to do is group and sum the Amount column by the lowest common Parent (ie the ones who's parent id is NULL). ``` ID Amount Parent_ID 1 100 NULL 2 150 1 3 50 1 4 75 3 5 25 4 6 125 NULL 7 50 6 8 50 7 9 100 8 ``` Expected results: ``` ID SUM 1 400 6 325 ``` As you can see, it only retuns two records, the ones who dont have a parent, meaning they are top level items. The Sum column is the sum of all of its children's `Amount` recursively, so `ID` = 1 is the sum of 1,2,3,4 and 5. and `ID` = 6 is the sum of 6,7,8, and 9.