sql - Find duplicate ID's with different fields -

sql - Find duplicate ID's with different fields -

i have table contain userid's , departments. userid's can belong several departments combo makes unique.

however have been trying query trying find userid belongs either 1 of 2 departments (hr or customer).

select userid, dept, count(*) total mytable grouping userid having count(*) = 1

however still brings duplicates if userid has both departments guess because combo makes unique record.

what this

userid | section | total 1 hr 1 2 client 1 3 client 1 1 client 1 3 hr 1

but trying this

userid | section | total 2 client 1

where instances of userid belonging both departments not included if belong 1 or other.

this should job

select t1.userid, dept, t2.total mytable t1 inner bring together ( select userid, count(*) total table1 grouping userid having count(*) = 1 ) t2 on t1.userid = t2.userid

sql sql-server database