Query to count and filter records

Transact-SQL
1

DATA:

ACCOUNT PARTNER ADDRS_NUM
HL124 2341 ASD123
HL124 2341 SDF234
HL124 234N SDF234
HG225 45BV QWE345
HG225 72AA QWE345
UY150 8846 CCC204

Need to get ACCOUNT that has more than one unique PARTNER & ADDRS_NUM are different. In above case, only we need to get HL124

I tried select query and then thought of filtering on the counts, but select gives weird o/p and also taking time.

sel count(ACCOUNT) over (partition by ACCOUNT), count(PARTNER ) over (partition by ACCOUNT), count(ADDRS_NUM ) over (partition by ACCOUNT) from table

Would like to know efficient way to get result.

2 
SELECT 
    Account
FROM
    YourTable
GROUP BY
    Account, Partner
HAVING
    COUNT(*) > 1;
3

hi

i do not think James SQL is right .. please excuse me James
Need to get ACCOUNT that has more than one unique PARTNER & ADDRS_NUM are different.

create data script

drop table if exists #DATA

create table #DATA(ACCOUNT varchar(10) ,PARTNER varchar(10) ,ADDRS_NUM varchar(10) )
insert into #DATA select 'HL124','2341','ASD123'
insert into #DATA select 'HL124','2341','SDF234'
insert into #DATA select 'HL124','234N','SDF234'
insert into #DATA select 'HG225','45BV','QWE345'
insert into #DATA select 'HG225','72AA','QWE345'
insert into #DATA select 'UY150','8846','CCC204'

select 
     ACCOUNT
   , partner 
   , sum(case when Partner <> ADDRS_NUM then 1 else 0 end)  from #DATA 
group by 
      ACCOUNT, partner 
having 
    sum(case when Partner <> ADDRS_NUM then 1 else 0 end) > 1

image

4

apologies for my wordings.

Need to get ACCOUNT that has more than one unique PARTNER
and,
at the same ADDRS_NUM should be different.

incase of HG225, though it has more than one unique PARTNER, but ADDRS_NUM are same.
Incase of UY150, it has only one unique PARTNER

5

hi

i think i understood you

; with cte as 
(
  select 
       ROW_NUMBER () OVER (partition by account , Partner order by addrs_num) as rn , 
	   * 
  FROM 
     #DATA 
) 
select 
   Account    
from 
   ( select * from cte where rn = 1 ) a 
group by 
       Account 
having 
     min(addrs_num) <> max(addrs_num)

image

6 

select ACCOUNT
from #data
group by ACCOUNT, PARTNER
having count(*) > 1 and count(distinct addrs_num) > 1