3

I Have a table in the below format.This is a self referential table where each record points to its parent record.

NODE_ID  PARENT_ID  COUNT
1        0          NULL
2        1          NULL
3        2          10
4        2          12
5        0          NULL
6        5          NULL
7        6          NULL
8        7          12

I want the output to be in below format.The count of parent should be the sum of count of leaf childs. Note: Only the leaf childs will contain the count. I want to roll it up till parents.

NODE_ID  PARENT_ID  COUNT
1        0          22
2        1          22
3        2          10
4        2          12
5        0          12
6        5          12
7        6          12
8        7          12

Please help.

gkarya42
  • 429
  • 6
  • 22
  • @GiorgosBetsos I think you misread the output. Node `2` has two children with a total count of `22`. In turn, node `1` only has `2` as a child, who has a count of `22`. He probably needs a recursive query of some sort. – Tim Biegeleisen Sep 06 '16 at 09:10
  • @Tim Biegeleisen You are right.I want the sum of counts of direct childs. – gkarya42 Sep 06 '16 at 09:12

1 Answers1

1

Well, I couldn't think of anything simpler:

;WITH GetLevelsCTE AS (
   SELECT NODE_ID, PARENT_ID, COUNT, level = 1, ROOT = NODE_ID
   FROM mytable
   WHERE PARENT_ID = 0

   UNION ALL

   SELECT t1.NODE_ID, t1.PARENT_ID, t1.COUNT, level = t2.level + 1, t2.ROOT
   FROM mytable AS t1
   JOIN GetLevelsCTE AS t2 ON t2.NODE_ID = t1.PARENT_ID
), MaxLevelCTE AS (
   -- Get MAX level per root NODE_ID
   SELECT MAX(level) AS max_level, ROOT
   FROM GetLevelsCTE
   GROUP BY ROOT
), GetCountCTE AS (
   -- Anchor query: start from the bottom 
   SELECT t1.NODE_ID, t1.PARENT_ID, t1.COUNT, t1.level
   FROM GetLevelsCTE AS t1
   JOIN MaxLevelCTE AS t2 ON t1.ROOT = t2.ROOT 
   WHERE t1.level = t2.max_level

   UNION ALL

   -- Recursive query: get counts of next level
   SELECT t1.NODE_ID, t1.PARENT_ID, t2.COUNT, t1.level
   FROM GetLevelsCTE AS t1
   JOIN GetCountCTE AS t2 ON t1.level = t2.level - 1 AND t1.NODE_ID = t2.PARENT_ID
)
SELECT NODE_ID, PARENT_ID, SUM(COUNT) AS COUNT
FROM GetCountCTE
GROUP BY NODE_ID, PARENT_ID
ORDER BY NODE_ID

Short explanation:

  • GetLevelsCTE is used to assign a level number to every node of the tree.
  • MaxLevelCTE uses the previous CTE in order the obtain the maximum level of the tree.
  • GetCountCTE uses both previous CTEs in order to traverse the tree from the bottom to the parent node. This way, the COUNT is propagated to the parent node.
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • :Thanks for replaying. Your query is only valid if there is only one level of hierarchy. If in the same table i have two top level parents (node with no parents or parent id 0), then it will not work out .I hope you got my concern. – gkarya42 Sep 06 '16 at 09:57
  • I have updated the question table. Parent id 0 means the root of the tree hierarchy . – gkarya42 Sep 06 '16 at 10:17
  • My mistake, issue is replicated if the dept of trees in the table is different.edited the question. – gkarya42 Sep 07 '16 at 08:50
  • Corrected in question. – gkarya42 Sep 07 '16 at 09:06