CODEWITHSUNDEEP
sqlsql-server
BongReyes
BongReyes

Reputation: 205

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

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.

Upvotes: 1

Views: 40

Answers (2)

Anon
Anon

Reputation: 10918

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]

Upvotes: 0

Lamak
Lamak

Reputation: 70678

;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

Upvotes: 3

Related Questions