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!

@X-Zero - Not really, since in the example the COUNT doesn't take into account the column Endorsement — Lamak
– Lamak, Commented Aug 16, 2012 at 21:45
@Lama - #headdesk# - boy, I must be tired. I was going, 'but the count is from the group policy, line, endorsement...' But of course it's the count of endorsements, not differentiated by it. Ouch — Clockwork-Muse
– Clockwork-Muse, Commented Aug 16, 2012 at 22:38
@GordonLinoff I went ahead and included which version I'm using. — Ashton Sheets
– Ashton Sheets, Commented Aug 16, 2012 at 23:00

Gordon Linoff · Accepted Answer · 2012-08-16 21:43:40Z

4

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.

answered Aug 16, 2012 at 21:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ashton Sheets Over a year ago

The WITH method worked for me. Go figure, it ended up showing me that not a single line had more than one endorsement so every count returned was 1. I changed the count to Product, Policy (since I knew that would have higher count) just to check that I had implemented your code correctly and it worked. I manually verified the counts. Thanks a bunch, Gordon!

Gordon Linoff Over a year ago

@AshtonSheets . . . If you are using SQL Server 2005 or later, then the first method with the window function should also work. Window functions (also called analytic functions) are very powerful and worth learning.

Ashton Sheets Over a year ago

I'll definitely look in to that. I tried it but must have done something wrong. I'll check it out some more. Thanks again :)

Jim · Accepted Answer · 2012-08-16 21:49:16Z

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

Collectives™ on Stack Overflow

Count Rows with same values after Join in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related