How to count group by a tree use oracle?

@denis 

To count group by a tree in Oracle, you can use a recursive query with the CONNECT BY clause to traverse the hierarchical data structure of the tree. Here is a general example of how you can use the COUNT function along with the CONNECT BY clause to count the number of nodes in each level of the tree:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

WITH RECURSIVE TreeHierarchy AS (
  SELECT id, parent_id, 1 AS Level
  FROM your_table
  WHERE parent_id IS NULL

  UNION ALL

  SELECT t.id, t.parent_id, th.Level + 1
  FROM your_table t
  JOIN TreeHierarchy th ON t.parent_id = th.id
)
SELECT Level, COUNT(*) AS NodeCount
FROM TreeHierarchy
GROUP BY Level
ORDER BY Level;

In this query:

1. The initial part of the CTE (Common Table Expression) named TreeHierarchy selects the root nodes (nodes with no parent) of the tree.
2. The recursive part of the CTE joins the current level of nodes with their children nodes, incrementing the Level by 1.
3. The final SELECT statement groups the nodes by Level and counts the number of nodes at each level.

You will need to adjust the table names and columns to match your specific tree structure. This query should give you a count of nodes at each level of the tree.