title: Stored procedure to count number of occurrences of a value in another column
body: <p>I would like, thanks to a <strong>stored procedure</strong>, to count in test_paternite, the number of occurrences of &quot;parent&quot; in &quot;cb&quot; column (image 1).
  For example, the parent &quot;ASPIELEC 01 1/1&quot; appears 1 time in cb column: (image 2)</p>
  <p><a href="https://i.stack.imgur.com/FxiMt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FxiMt.png" alt="image 1" /></a></p>
  <p><code>SELECT cb, parent, test_paternite FROM [database].[dbo].[Paternite] where cb='ASPIELEC 01 1/1' </code></p>
  <p><a href="https://i.stack.imgur.com/ljkBR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ljkBR.png" alt="image 2" /></a></p>
  <p>expected results for ASPIELEC 01 1/1:</p>
  <p><a href="https://i.stack.imgur.com/3dQe2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3dQe2.png" alt="enter image description here" /></a></p>
  <p>I tried:</p>
  <pre><code>USE [database]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[INS_MAJ_PATERNITE_V2] 
          @retcode int OUTPUT,
          @message VARCHAR(255) OUTPUT
      AS
  
  BEGIN
      
              ---- how many times parents appears in cb column
      
      WITH nb_parent_dans_cb
      AS
      (
          SELECT count(*) AS nb, cb 
          FROM Paternite
          where parent in (select cb from paternite)
          GROUP BY cb 
      )
  
      UPDATE Paternite
      SET Test_paternite = nb_parent_dans_cb.nb
      FROM Paternite
      INNER JOIN nb_parent_dans_cb ON paternite.cb = nb_parent_dans_cb.cb
  
  END
  </code></pre>
  <p>Is this the best way?</p>