count(name) > 1 [MS SQL 2005]

[anselme]

for MS SQL 2005

I want to find all users with same sName

CREATE TABLE [Users] (
[id_Users] [int] NOT NULL PRIMARY KEY CLUSTERED ,
[fName] [varchar] (100),
[sName] [varchar] (100)
)



I must get all the rows with count(sName) > 1

20 , Aldous Leonard, Huxley
26 , Bob, Huxley
.......

thank you for helping

[MAK]

use tempdb
go
CREATE TABLE [Users] (
[id_Users] [int] NOT NULL PRIMARY KEY CLUSTERED ,
[fName] [varchar] (100),
[sName] [varchar] (100)
)
go
insert into [Users] select 20 , 'Aldous Leonard', 'Huxley'
insert into [Users] select 26 , 'Bob', 'Huxley'
insert into [Users] select 21 , 'Aldous Leonard', 'Walter'
insert into [Users] select 22 , 'Bob', 'Smith'
insert into [Users] select 12 , 'Adam', 'Smith'
insert into [Users] select 11 , 'Carrie', 'Smith'
go

select count(id_users),sname from Users group by sname

--result
,sname
2,Huxley
3,Smith
1,Walter
select count(id_users),sname from Users group by sname having count(id_users)>1

--result
,sname
2,Huxley
3,Smith

select sname from Users group by sname having count(id_users)>1
--result

sname
Huxley
Smith

select * from users where sname in (select sname from Users group by sname having count(id_users)>1)
order by sname,fname
--result

id_Users,fName,sName
20,Aldous Leonard,Huxley
26,Bob,Huxley
12,Adam,Smith
22,Bob,Smith
11,Carrie,Smith

[anselme]

thanks a lot MAK