How to count the number of nodes in a SQL tree?

Question

SQL Server

The question is taken from here:
https://stackoverflow.com/questions/49636142/how-t...
There is a table tree. You need to count the number of nodes in the tree.
The table creation code and the recursive query itself to collect nodes:

INSERT INTO Organization_structure VALUES
(100, NULL),
(129, 100),
(134, 100),
(439, 129),
(450, 129),
(133, 134),
(133, 134),
(501, 439),
(602, 501);

with tree as
(
    select orgId, parentId,0 as tree_order, path = cast('root' as varchar(100)) 
    from   Organization_structure 
    where  parentID is null
    union all
    select os.orgId, os.parentId, 1 + tree_order as tree_order,
           path = cast(tree.path + '/' + right(('000000000' + os.orgId), 10) as varchar(100))
    from   Organization_structure os
    join   tree 
    on     tree.orgId = os.parentId
)
select orgId, tree_order, path, t2.cnt 
from tree
cross apply (select count(*) cnt from tree t1 where t1.path like tree.path + '%') t2
order by tree_order;

In path, it stores the path to this node, all previous nodes: The
root/134/133
query displays the correct number of nodes: 11.
But there is one more branch to add (i.e. it has parentid = null, this is the root, the main node):
(1000, null) ,
and let it, for example, have no other nodes (whose parentId = 1000), then this query will also display 11 nodes for it, although it should display 1 (this root itself, which has parentid = 1000).
If we add a new node to the node: (2000, 1000), then we get 2 nodes in the tree, but the query will still display 11:

Query link for one root:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6. ..
The second root is demonstrated here:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6...
subd ms server
The where clause should be removed so that the query works not only for the first tree, but for all.
But then he also considers the number of nodes to be the same for ALL: 27
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1...
In the path, as I understand it, inaccurate information for the second tree will be stored, so it will display an inaccurate number in nodes.
What needs to be changed in the query so that it correctly counts the number of nodes for several trees?
There are no solutions in Google where there is more than one tree.
Thanks in advance

Answer 1

I want to display count of children for every node in the tree.

Subsidiaries , not all ! All the above solutions are incorrect because the node itself is also considered.
Let's move from theory to practice .