Get list of customers with more than 1 address

[Dukester0122]

I have a database with customers and some with more than one address. I'm trying to capture the customers with more than one address and return the resultset. I tried to do count(ADRSCODE) and distinct but never really got the result I wanted. Appreciate any help from the group, the following are the fields from the table:
Customer name - CUSTNAME
Address Code - ADRSCODE

 

[gmmastros]

Select CUSTNAME, Count(ADRSCODE) As AddressCount
From   YourTableName
Group By CUSTNAME
Having Count(ADRSCODE) > 1

Or even.....

Select *
From   YourTable
       Inner Join (
          Select CUSTNAME, Count(ADRSCODE) As AddressCount
          From   YourTableName
          Group By CUSTNAME
          Having Count(ADRSCODE) > 1
          ) As A
          On YourTable.CUSTNAME = A.CUSTNAME

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Dukester0122]

I tried this and was failing:

select CUSTNAME from CUSTTABLE
where CUSTNAME in (select count(adrscode) from CUSTTABLE)

 

[Dukester0122]

Thanks George, first script is enough.

 

[Dukester0122]

I'm trying to add another field - CUSTNBR (tables PK) and i'm getting this error:

Msg 8120, Level 16, State 1, Line 1
Column 'TABLE.CUSTNMBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[bborissov]

And if you want to get all customers that have DIFFERENT addresses (based on George's example):

Select *
From   YourTable
       Inner Join (
          Select CUSTNAME, Count(DISTINCT ADRSCODE) As AddressCount
          From   YourTableName
          Group By CUSTNAME
          Having Count(DISTINCT ADRSCODE) > 1
          ) As A
          On YourTable.CUSTNAME = A.CUSTNAME

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

SQL Server 2005 and up solution:

select * from (select Customer.*, Count(ADRSCODE) OVER (Partition by CustName) as CountAddresses) X where CountAddresses > 1

 

[markros]

Correction:

select * from (select Customer.*, Count(ADRSCODE) OVER (Partition by CustName) as CountAddresses from Customer) X where CountAddresses > 1

 

[Dukester0122]

Perfect!

 

[markros]

You're welcome.