title: Include a column to count records with a specific value
body: <p>I want to return all data in a table and append a column that counts the number of records in a subset (say, the number of houses in a neighborhood).</p>
  
  <p>I tried</p>
  
  <pre><code>CASE
      WHEN EXISTS (SELECT 1 as [parcels]
          FROM dbo.parcels p2
          WHERE p2.Neighborhood = p.Neighborhood)
      THEN COUNT([parcels]) END  -- can't count outside subquery
          as [TotalProps]
  </code></pre>
  
  <p>The subquery itself returns a value of 1 for each property record in any given neighborhood, but I can't count/sum the [parcels] outside of the subquery in a THEN statement.</p>
  
  <p>Input Table:</p>
  
  <pre><code>dbo.parcels
  ID    Address        Neighborhood
  ==    =======        ============
  1     123 Main St    MITO
  2     124 Main St    MITO
  3     200 2nd St     MITO
  4     201 2nd St     MITO
  5     5 Park Ave     FAIRWIND
  6     1600 Baker St  GALLERY
  7     1601 Baker St  GALLERY
  8     1602 Baker St  GALLERY
  </code></pre>
  
  <hr>
  
  <pre><code>SELECT *, &lt;&lt;&lt;COUNT(neighborhood props)&gt;&gt;&gt; as [TotalProps]
  FROM dbo.parcels p
  </code></pre>
  
  <p>Expected Output:</p>
  
  <pre><code>ID    Address        Neighborhood  TotalProps
  ==    =======        ============  ==========
  1     123 Main St    MITO          4
  2     124 Main St    MITO          4
  3     200 2nd St     MITO          4
  4     201 2nd St     MITO          4
  5     5 Park Ave     FAIRWIND      1
  6     1600 Baker St  GALLERY       3
  7     1601 Baker St  GALLERY       3
  8     1602 Baker St  GALLERY       3
  </code></pre>