Calculate the number of descendants recursively

Question

I have a navigation table that joins itself using ParentId. I'm trying to calculate how many descendants each record has, I know that I need to increase the counter in recursion, I'm just not sure how to do this!

Any help would be greatly appreciated!

CREATE TABLE [dbo].[Navigation](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AnchorText] [varchar](50) NOT NULL,
    [ParentID] [int] NULL)

insert into Navigation
select 'Level 1', null
union
select 'Level 2', 1
union
select 'Level 3', 2
union
select 'Level 4', 3


WITH NavigationCTE (Id, AnchorText, ParentId, NumberofDescendants) as
(
      Select Id, AnchorText, ParentId, 'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      union ALL 

      select nav.Id, nav.AnchorText, nav.ParentId,  'Number of Descendants Here' as NumberofDescendants
      from dbo.Navigation nav

      join Navigation ON nav.ParentId = nav.Id
)


SELECT * FROM NavigationCTE

EDIT Added level and added to recursion:

WITH NavigationCTE (Id, AnchorText, ParentId, Level) as
(
      Select nav.Id, nav.AnchorText, nav.ParentId, 0 as Level
      from dbo.Navigation AS nav

      UNION ALL 

      select nav.Id, nav.AnchorText, nav.ParentId, Level + 1
      from dbo.Navigation AS nav

      join Navigation AS nav2 ON nav.ParentId = nav2.Id
)


SELECT * FROM NavigationCTE

Answer 1

, . . , , 0 .

:

WITH NavigationCTE  AS
(   
    SELECT navA.[Id], navA.ParentId, 0 AS depth_lvl
    FROM Navigation as navA

    UNION ALL

    SELECT navB.Id, navB.ParentId, depth_lvl + 1
    FROM Navigation AS navB
    JOIN NavigationCTE AS nav_cte_a
        --ON navB.ParentId = nav_cte_a.Id
        ON nav_cte_a.ParentId = navB.Id
)
SELECT Id, ParentId, coalesce(max(depth_lvl),0)
FROM NavigationCTE
GROUP BY Id, ParentId
ORDER BY Id, ParentId