Count Rows with same values after Join in SQL

Question

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:

Product | Policy Number | Line Number | Endorsement Name

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.

This is what the query looks like right now:

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

The resulting table looks something like this

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

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.

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:

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

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.

I've tried doing, GROUP BY with HAVING 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 WHERE statements and some Order By as well.

EDIT: I'm using Microsoft SQL Server Management Studio 2010 to execute these queries

Thanks!

Answer 1

Give this a whirl:

Select pd.ProductName AS Product
,p.PolicyNumber AS Policy
,l.LineNumber AS Line
,e.EndorsementName AS Endorsement
,cnt

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
INNER JOIN (
    Select pd.ProductName AS subProduct
    ,p.PolicyNumber AS subPolicy
    ,l.LineNumber AS subLine
    ,COUNT(*) AS cnt

    FROM Lines l
    INNER JOIN Policies p On p.PolicyID = l.PolicyID
    INNER JOIN Product pd on pd.ProductID = p.ProductID
    GROUP BY pd.ProductName, p.PolicyNumber, l.LineNumber
    ) AS Sub ON pd.ProductName = subProduct, p.PolicyNumber = subPolicy, l.LineNumber = subLine

Answer 2

This depends on the database. If you have window functions, you can do it quite simply:

Select pd.ProductName as Product, p.PolicyNumber as Policy,
       l.LineNumber Line, e.EndorsementName as Endorsement,
       count(*) over (partition by policy, line) as Policy_Line_Count
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

If you don't have the windows functions, then you can use the WITH statement to simplify the query:

with t as (
         Select pd.ProductName as Product, p.PolicyNumber as Policy,
            l.LineNumber 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
   )
select t.*, tpl.cnt
from t join
     (select Policy, Line, count(*) as cnt
      from t
      group by Policy, Line
     ) tpl
     on t.Policy = tpl.Policy and
        t.Line= tpl.Line

Finally, you might be using mysql that has neither the WITH statement nor windows functions. In this case, you can create a view and use the above query. Or, you can take the original query and put it in a temporary table and use the above query. Or, you take the original query and repeat it twice following the example.