MySQL Group By Lowest Common Parent ID

Question

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.

Are there set numbers of nesting? Ie do you stop at 5 or could it go on forever? — xQbert, Apr 20 '12 at 17:58
In theory it can go forever, but if there is no solution for it going forever i guess i could take one that only goes to 5 or so. — user380527, Apr 20 '12 at 17:59
Setup environment for testing: http://www.sqlfiddle.com/#!2/b7a79 — mellamokb, Apr 20 '12 at 18:00
many questions about recursive queries against MySQL... Can't be done without actual stored procedure to handle each recursive level going up the chain. — DRapp, Apr 20 '12 at 18:00
MySQL doesn't support hierarchical queries. http://forums.mysql.com/read.php?113,136927,155954#msg-155954 will give some pointers and there are some examples here on SO too.. looking them up now... and http://stackoverflow.com/questions/5749385/mysql-hierarchical-storage-searching-through-all-parent-grandparent-etc-nodes — xQbert, Apr 20 '12 at 18:00
You may also find this informative: http://explainextended.com/2010/04/18/hierarchical-query-in-mysql-limiting-parents/ — mellamokb, Apr 20 '12 at 18:03
I just found this which i may be able to use: http://stackoverflow.com/questions/3438111/mysql-stored-procedure-that-calles-itself-recursively — user380527, Apr 20 '12 at 18:33

score 6 · Accepted Answer · answered Apr 20 '12 at 18:10

6

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

answered Apr 20 '12 at 18:10

mellamokb

56,094
12
110
136

1

Great answer. I would like to give you +2. One for the coalesce, which I haven't seen before and an extra +1 for sqlfiddle. What a great tool!! – barsju Apr 20 '12 at 18:21
1

Ok, so this is a good workaround, but i am seeing a weird bug with it. In my table i also have a Name column, which i want to select along with the query. The problem is that when i select the Name as well it is wrong (the selected name is not the same as the name of the item with the Root id). See this example (note i had to rearrange the inserts to get this to happen): http://www.sqlfiddle.com/#!2/dbd58/1 – user380527 Apr 20 '12 at 20:53
1

Nevermind, it looks like i needed to coalesce the Name as well: `coalesce(t5.Name,t4.Name,t3.Name,t2.Name,t1.Name) as Name`. http://www.sqlfiddle.com/#!2/dbd58/4 – user380527 Apr 20 '12 at 20:57

score 0 · Answer 2 · answered Apr 20 '12 at 18:01

To achieve what you're looking for, you'll need a recursive stored procedure because you don't want something that will limit you to how deep the structure goes.

I think it would be easier to change your data structure. Add a column that relates each child to its top level parent. That way you can get what you want with a simple GROUP BY.

MySQL Group By Lowest Common Parent ID

2 Answers2