title: how to count total left and total right child of a user in downline in a MLM binary Tree in SQL CTE
body: <p>I am facing some of problem while getting the following result from a parent child relationship please check the follwing table structure.<br>
  <img src="https://i.stack.imgur.com/onw0P.png" alt="enter image description here"></p>
  
  <p>The above is the table structure and the tree structure is like below image.
  <img src="https://i.stack.imgur.com/SQGbc.png" alt="enter image description here"></p>
  
  <p>in case of my scenario here is an MLM tree  in which every person have down line members on left side and right side.so i need to calculate this for the current login user in their down line</p>
  
  <p>So i need the right side summary recursively  thanks in advance</p>
  
  <pre><code>    ALTER Function [dbo].[F_SearchUsersTreeByParent](@id as int)
  Returns table as 
  Return
  
  
  WITH CTE_Table (id, FullName,UserName, RefferdByID,Levels,LevelPrice, IsPurchasedProduct)
  AS
  (
  SELECT id, FullName,UserName,-1 as RefferdByID,Levels,LevelPrice,IsPurchasedProduct
  FROM Registration WHERE id = @id and Registration.IsPurchasedProduct=1
  UNION ALL
  SELECT Registration.id, Registration.FullName,Registration.UserName, Registration.RefferdByID,Registration.Levels,Registration.LevelPrice, Registration.IsPurchasedProduct FROM Registration
  JOIN CTE_Table ON Registration.RefferdByID = CTE_Table.id
  where Registration.IsPurchasedProduct=1
  )
  
  SELECT id, FullName,UserName, RefferdByID,Levels,LevelPrice, IsPurchasedProduct,
  
  (Select count(*)-1 from dbo.F_CountRefered(id) where id&lt;&gt;abc.id) as RefCount
   FROM CTE_Table as abc
  </code></pre>