Count All Child Nodes of Hierarchical Data in a Table

Count children from hierarchy path

You can do:

select
  t.*,
  (select count(*) from t c where c.path like t.path || '/%') as c_count,
  i.income + (
    select coalesce(sum(i.income), 0) from t c join i on i.user_id = c.id
     where c.path like t.path || '/%'
  ) as c_income
from t
left join (
  select user_id, sum(income) as income from i group by user_id
) i on i.user_id = t.id

Result:

 id  name   path    c_count  c_income 
 --- ------ ------- -------- -------- 
 1   John   /1      2        820      
 2   Mark   /2      1        1020     
 3   Kevin  /1/3    1        500      
 4   Sarah  /1/3/4  0        null     
 5   Andy   /2/5    0        680      

See example at DB Fiddle.

how to find all the child nodes in hierarchical tree

For 3 levels, you can have it with

    SELECT * FROM 
-- root
(
SELECT * 
FROM your_table WHERE id = 1 
) as root
UNION ALL
-- level1
(
SELECT * FROM your_table WHERE parentId = 1 ) 
)
UNION ALL
-- level2
(
SELECT * FROM your_table WHERE parentId IN (SELECT id FROM your_table WHERE parentId = 1 ) 
)

But for deep depth, I would suggest you implement common approach such as Nested Set Model (need to add extra columns), please take a look on this

Counting all childs and sub-childs of each node in a hierarchy

if you want it for a specific ID (as you mentioned for example ID=1) you can left join them all on idparent and id and use count(distinct):

select a.ID,
       count(distinct b.id) childB,
       count(distinct c.id) childC,
       count(distinct d.id) childD
from      tableA a 
left join tableB b on b.parentID = a.ID
left join tableC c on c.parentID = b.ID
left join tableD d on d.parentID = c.ID
where a.ID=1
group by a.ID;

here is a fiddle DEMO.

Counting number of children in hierarchical SQL data

Using a CTE would get you what you want.

• Recursively go through all children, remembering the root.
• COUNT the items for each root.
• JOIN these again with your original table to produce the results.

Test Data

DECLARE @Data TABLE (
  ID INTEGER PRIMARY KEY
  , ParentID INTEGER
  , Text VARCHAR(32)
  , Price INTEGER
)

INSERT INTO @Data
  SELECT 1, Null, 'Root', NULL
  UNION ALL SELECT 2, 1, 'Flowers', NULL
  UNION ALL SELECT 3, 1, 'Electro', NULL
  UNION ALL SELECT 4, 2, 'Rose', 10
  UNION ALL SELECT 5, 2, 'Violet', 5
  UNION ALL SELECT 6, 4, 'Red Rose', 12
  UNION ALL SELECT 7, 3, 'Television', 100
  UNION ALL SELECT 8, 3, 'Radio', 70
  UNION ALL SELECT 9, 8, 'Webradio', 90

SQL Statement

;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    @Data
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN @Data d ON d.ParentID = cte.ID
)
SELECT  d.ID, d.ParentID, d.Text, d.Price, cnt.Children
FROM    @Data d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

How to find total number of childs and Number of grand child levels under a hierarchical list parent in C#

We can use the below method to get depth of a hierarchical list

public static IEnumerable<Tuple<int, T>> FindDepthOfTreeNode<T>(T root, Func<T, IEnumerable<T>> children)
        {
            var stack = new Stack<Tuple<int, T>>();

            stack.Push(Tuple.Create(1, root));

            while (stack.Count > 0)
            {
                var node = stack.Pop();

                foreach (var child in children(node.Item2))
                {
                    stack.Push(Tuple.Create(node.Item1 + 1, child));
                }
                yield return node;
            }
        }

and just use it like below

int depth = menuItem.Children == null ? 0 : menuItem.Children
                                .SelectMany(y => FindDepthOfTreeNode(y, xs => xs.Children ??
                                Enumerable.Empty<NavBarItem>()))
                                .Max(xs => xs.Item1);

For getting the total child count in a hierarchical list node we can use below method.

public static int GetChildCountFromTree(this NavBarItem obj)
        {
            var queue = new Queue<NavBarItem>();
            queue.Enqueue(obj); // Note that you can add first object in the queue constructor

            var result = 0;

            while (queue.Any())
            {
                var current = queue.Dequeue();
                result++;
                if (null != current.Children)
                {
                    foreach (NavBarItem inner in current.Children)
                    {
                        queue.Enqueue(inner);
                    }

                    current.Last =  true;
                }
            }

            return result;
        }

and we can use it like below

ourHirarchyNode.GetChildCountFromTree();

LAG All Previous Rows - Parent Child Database Find All Total Children Count From Each Parent

Here is solution that works if you don't care for id order. Unfortunately, I don't know how to make it ordered as you did in desired output. If nothing I hope it helps you come to solution as data is good.

WITH cte as (
 SELECT p.Id as 'PID', c.Id as 'CID' FROM nodes p left join nodes c on p.id = c.ParentId
 UNION ALL
 SELECT c.PID,p.ID FROM cte c JOIN nodes p ON c.CID=p.ParentId
)
SELECT PID as id, count(*) as 'Children Count' FROM cte where CID IS NOT NULL GROUP BY PID 
UNION ALL
SELECT PID, 0 FROM cte WHERE CID IS NULL GROUP BY PID
ORDER BY PID ASC

---

Related Topics