title: Count Rows with same values after Join in SQL
body: <p>How do I add a column that displays a count for the total number of rows that have the same values in two columns, with multiple tables being joined already. After all the joins and where statements I end up with a table that has the following columns:</p>
  
  <p>Product | Policy Number | Line Number | Endorsement Name</p>
  
  <p>There are a lot more columns in the table but these are the ones I need to reference.
  There is a one to Many relationship from Product to Policy Number, a one to Many relationship from Policy Number to Line Number, and a possible one to Many relationship from Line Number to Endorsement or no matching relationship. </p>
  
  <p>This is what the query looks like right now:</p>
  
  <pre><code>Select pd.ProductName AS Product
  ,p.PolicyNumber AS Policy
  ,l.LineNumber AS Line
  ,e.EndorsementName AS Endorsement
  
  FROM Lines l
  INNER JOIN Policies p On p.PolicyID = l.PolicyID
  LEFT OUTER JOIN Endorsements e on e.EndorsementID = l.EndorsementID
  INNER JOIN Product pd on pd.ProductID = p.ProductID
  </code></pre>
  
  <p>The resulting table looks something like this</p>
  
  <pre><code>Product     Policy     Line     Endorsement
  CH           8001       1            A
  CH           8001       2            A
  CH           8001       3            A
  CH           8001       3            B
  CH           8002       1            D
  CH           8002       2            D
  CH           8002       3            A
  PP           9001       1            NULL
  PP           9001       2            NULL
  PP           9002       1            A
  PP           9002       2            A
  PP           9002       2            B
  </code></pre>
  
  <p>What I need to do is count the number of endorsements for each policy line. In the example above, I would see that for Product CH, the only line item with multiple endorsements is Policy 8001 - Line 3. For Product PP the only line item with multiple endorsements is Policy 9002 - Line 2.</p>
  
  <p>First thing I want to have is a new column that displays a count for every row that shows the total number of occurrences with that row's same Policy and Line values. The table would then look like this:</p>
  
  <pre><code>Product     Policy     Line     Endorsement     Line Endorsement Count
  CH           8001       1            A                   1
  CH           8001       2            A                   1
  CH           8001       3            A                   2
  CH           8001       3            B                   2
  CH           8002       1            D                   1
  CH           8002       2            D                   1
  CH           8002       3            A                   1
  PP           9001       1            A                   1
  PP           9001       2            A                   1
  PP           9002       1            A                   1
  PP           9002       2            A                   2
  PP           9002       2            B                   2
  </code></pre>
  
  <p>After I successfully achieve this counting, I then want a separate query that would only return rows with policies that don't have any lines with multiple endorsements, so in this case, policies 8001 and 9002 would not show up.</p>
  
  <p>I've tried doing, <code>GROUP BY</code> with <code>HAVING</code> and all kinds of different things I've seen in different posts and adapted to my needs but have not found a solution that works for this specific example. Keep in mind I also already have quite a few <code>WHERE</code> statements and some Order By as well.</p>
  
  <p><strong>EDIT:</strong>
  I'm using Microsoft SQL Server Management Studio 2010 to execute these queries</p>
  
  <p>Thanks!</p>