How do I get rows which have multiple values on the other column in sql server?

Question

I have a script here to get all account numbers that have multiple owners but I want to remove those rows which present 1 account_number = 1 account_owner. I want to get records that present 1 account_number >= one account_owner.

select ACCT_NUMBER, ACCT_OWNER from [DW].[ACCT DETAILS] WITH(NOLOCK) 
where ACCT_NUMBER in (select distinct(ACCT_NUMBER)
                      from [DW].[TDMA_DIM_DETAILS] WITH(NOLOCK)
                      group by ACCT_NUMBER
                      )
group by ACCT_NUMBER, ACCT_OWNER

Here are the results..

    ACCT_NUMBER  ACCT_OWNER
    1359180      Spearman, John
    1359190      Grifman, Karen
    1359210      Spearman, John

    1359220      Adams, Craig
    1359220      Biga, Lou
    1359220      Wright, Gerald

    1359230      Levine, Renee
    1359250      Reitwiesner, John
    1359260      Skowronski, Cindi

    1359510      Cordova, Diana
    1359510      Macfarlane, Linda
    1359510      Marquez, Suzanne

I was hoping to see results like the one I got from account numbers 1359220 and 1359510, because there are also other account numbers that have multiple owners.

I've tried using having(count(distinct ACCT_OWNER) >1 but it didn't help.

If I understand correctly, you want to select only lines where the account_number is associated with several account_owners. If so, you could do a subquery selecting account_number and count(account_owner) where the count > 1, and use that to filter the main query using either where or join. — Tal
– Tal, Commented Oct 22, 2014 at 19:52

Lamak · Accepted Answer · 2014-10-22 19:49:54Z

3

;WITH CTE AS
(
    SELECT  ACCT_NUMBER, 
            ACCT_OWNER
    FROM [DW].[ACCT DETAILS]
    WHERE ACCT_NUMBER IN (  SELECT ACCT_NUMBER
                            FROM [DW].[TDMA_DIM_DETAILS])
    GROUP BY ACCT_NUMBER, 
             ACCT_OWNER
), CTE2 AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY ACCT_NUMBER)
    FROM CTE
)
SELECT *
FROM CTE2
WHERE N > 1

answered Oct 22, 2014 at 19:49

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

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

1 Comment

BongReyes Over a year ago

You are a blessing. Thank you.

Anon · Accepted Answer · 2014-10-22 20:20:09Z

0

SELECT
  [ACCT_NUMBER]
 ,[ACCT_OWNER]
 ,COUNT(*) OVER(PARTITION BY ACCT_NUMBER)
FROM [DW].[ACCT DETAILS] a
INNER JOIN [DW].[TDMA_DIM_DETAILS] b
  ON a.[ACCT_NUMBER] = b.[ACCT_NUMBER]
GROUP BY [ACCT_NUMBER],[ACCT_OWNER]
EXCEPT
SELECT
  [ACCT_NUMBER]
 ,[ACCT_OWNER]
 ,1
FROM [DW].[ACCT DETAILS]

answered Oct 22, 2014 at 20:20

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Collectives™ on Stack Overflow

How do I get rows which have multiple values on the other column in sql server?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related