How to pull only account ids that have multiple adddresses?

Question

How to pull only account ids that have multiple adddresses?

wallywizard

Ten Centuries

Points: 1004
More actions
November 13, 2020 at 1:44 pm

#3807531
I am at a wall with this. I thought it was going to be easy but clearly I am missing something. There are some account ids that only have 1 address and other account ids that have multiple addresses. I need to pull ONLY account ids that only have multiple addresses.
I tried to do a count on "acct_id" since, each row is technically a new location but the output is not looking correct.
Here are my columns
acct_id | business_name | street | city | state| zip

Does anyone know what I am doing wrong or is there another way I should be doing this?
```
SELECT 

COUNT (ACCT_ID) AS TOTAL_ACCT_NUM,
ACCT_ID,
BUSINESS_NAME,
STREET,
CITY,
STATE,
ZIP_CODE

FROM my_table

GROUP BY
ACCT_ID,
BUSINESS_NAME,
STREET,
CITY,
STATE,
ZIP_CODE

ORDER BY ACCT_ID
```

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

can you provide DDL and sample data to help. If they have multiple addresses but the same acct_id, then you can do something like

select Acct_id, count(1) from my_table group by acct_id having count(1) > 1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

wallywizard Ten Centuries Points: 1004 More actions · Answer 2

wallywizard

Ten Centuries

Points: 1004

November 13, 2020 at 1:59 pm

#3807539

silly question.. but how do I give you the DDL?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Try this instead

SELECT TOTAL_ACCT_NUM = COUNT(ACCT_ID) OVER (PARTITION BY ACCT_ID)
      ,ACCT_ID
      ,BUSINESS_NAME
      ,STREET
      ,CITY
      ,STATE
      ,ZIP_CODE
FROM my_table
ORDER BY ACCT_ID;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

I can't test these since you didn't provide directly usable test data:

--if you need only the acct_ids
SELECT acct_id, COUNT(*) OVER() AS total_accts
FROM my_table
GROUP BY acct_id
HAVING MIN(street + '?' + city + '?' + state + '?' + zip) <>
    MAX(street + '?' + city + '?' + state + '?' + zip) 

--if you need the acct_ids and the addresses
SELECT mt.*, COUNT(*) OVER() AS total_accts
FROM (
    SELECT acct_id
    FROM my_table
    GROUP BY acct_id
    HAVING MIN(street + '?' + city + '?' + state + '?' + zip) <>
        MAX(street + '?' + city + '?' + state + '?' + zip) 
) AS acct_ids_with_dup_addresses
INNER JOIN my_table mt ON mt.acct_id = acct_ids_with_dup_addresses.acct_id
ORDER BY acct_id, street, city, state, zip

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

as_1234 SSCrazy Points: 2865 More actions · Answer 5

wallywizard wrote:

silly question.. but how do I give you the DDL?

There is some guidance here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help