I have a table witch have duplicate records. I want to select only unique record with how many time repeated form table.
My table structure as
CREATE TABLE [dbo].[EmpInfo]
(
[ID] INT IDENTITY PRIMARY KEY,
[EmpName] VARCHAR(100)
)
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES('Chris Roberts')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Brad Tutterow')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES('Chris Roberts')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Brad Tutterow')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Paul Mendoza')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Zack Peterson')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Paul Mendoza')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Zack Peterson')
INSERT INTO [dbo].[EmpInfo] ([EmpName]) VALUES ('Vijay Shukla')
Thank in advance
AVADHESH PATEL
07-Jan-2013you can use below sql query
SELECT [EmpName], COUNT(*) TotalCount FROM [dbo].[EmpInfo] GROUP BY [EmpName] HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC