CODEWITHSUNDEEP
sqljoin
CraigRB89
CraigRB89

Reputation: 33

SQL Join Duplicate records

I'm trying to join two tables. Some jobs as you will notice below are duplicates. These are the only ones I'm having an issue on. When I join and Sum the premium instead of getting the expected result of $100, I'm seeing $200. Any ideas on how to get the query to not multiply * 2? Thanks!

Tbl A

ABC123     2015-02-27     1     QQ123
ABC123     2015-02-27     1     QQ123

Tbl B

ABC123, 100

SQL:

SELECT 
    RP.POLNUMBER, RP.EFFDATE, SUM (LP.PREMIUM)
FROM 
    TBL_A RP 
INNER JOIN 
    TBL_B LP ON RP.POLNUMBER = LP.POLNUMBER
WHERE 
    RP.MOSTRECENTMODEL = 1 AND RP.POLNUMBER = 'ABC123'

Upvotes: 1

Views: 89

Answers (2)

Prudhvi Konda
Prudhvi Konda

Reputation: 297

You can use this if the records is RP table are duplicate with respect to all attributes. Premium value should be the same for all similar Polnumber field; otherwise the result will be different again.

SELECT 
    RP.POLNUMBER, RP.EFFDATE, SUM (LP.PREMIUM)/COUNT(RP.POLNUMBER)
FROM 
    TBL_A RP 
INNER JOIN 
    TBL_B LP ON RP.POLNUMBER = LP.POLNUMBER
WHERE 
    RP.MOSTRECENTMODEL = 1 AND RP.POLNUMBER = 'ABC123'
GROUP BY (RP.POLNUMBER)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You've identified the problem correctly. The solution is to pre-aggregate the data before the join:

SELECT RP.POLNUMBER, RP.EFFDATE, LP.PREMIUM
FROM TBL_A RP INNER JOIN
     (SELECT LP.POLNUMBER, SUM(LP.PREMIUM) as PREMIUM
      FROM TBL_B
      GROUP BY LP.POLNUMBER
     ) LP
     ON RP.POLNUMBER = LP.POLNUMBER
 WHERE RP.MOSTRECENTMODEL = 1 AND RP.POLNUMBER = 'ABC123';

Actually, for performance purposes a correlated subquery probably should work better:

SELECT RP.POLNUMBER, RP.EFFDATE
       (SELECT SUM(LP.PREMIUM) as PREMIUM
        FROM TBL_B LP
        WHERE RP.POLNUMBER = LP.POLNUMBER
       ) as PREMIUM
FROM TBL_A RP 
WHERE RP.MOSTRECENTMODEL = 1 AND RP.POLNUMBER = 'ABC123';

Your filter conditions look highly selective. There is no reason to aggregate the entire table to just return results for a handful of policies.

Upvotes: 2

Related Questions