Finding a count of all duplicates with a different last colum-CodePudding

I have a table of insurance policies and i am trying to find all of the policies that have the exact same information except in a different zip code (last column).

if anyone has any suggestions on how to find this it would be greatly appreciated

Policy    | Expiry   |  State|     Region  |  InsuredValue  |   Construction |  BusinessType| Zip

100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10002
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10011
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10002
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10011
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10011
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10011
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10002
100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 10005

in this example I would want it to return:

Policy       Expiry     State    Region       InsuredValue    Construction    BusinessType   dupCount 

100242    | 2-Jan-21 |   NY |       East   |    1,617,630   |     Frame      |  Retail      | 3

dupCount is 3 because there are 3 distinct zipcodes for this data

CodePudding user response：

Just use group by all columns (except Zip) and count by Zip.

SELECT policy,
       expiry,
       state,
       region,
       insuredvalue,
       construction,
       businesstype,
       COUNT(distinct zip) AS dupcount
    FROM my_table
    GROUP BY policy,
             expiry,
             state,
             region,
             insuredvalue,
             construction,
             businesstype
    HAVING COUNT(distinct zip) > 1;

CodePudding user response：

You can try the below.

select t.Policy, t.Expiry, t.State, t.Region, t.InsuredValue, t.Construction, t.BusinessType, count(1) 
from (
  select distinct Policy, 
  Expiry, State, Region, 
  InsuredValue, Construction, 
  BusinessType, Zip from tablename) t
group by t.Policy, t.Expiry, t.State, t.Region, t.InsuredValue, t.Construction, t.BusinessType;