In my previous article SQL SERVER – Delete Duplicate Records – Rows, we have seen how we can delete all the duplicate records in one simple query. In this article we will see how to find count of all the duplicate records in the table. Following query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Delete Duplicate Records – Rows
187 Comments. Leave new
hello pinal,
Thanks.this is very helpful
regards
kalyan
hello pinal,
Tell me the difference between temporary table and table variable? Which is better?
regards
kalyan
Hi Shobha,
What I understand is: You need to display whole record (all columns) of record which is duplicated.
I have written to get Duplicate Rows from the table. You can use the ROW_Number() in SQL 2005.
To read more about it:
In this article, SELECT * FROM Table WHERE RowID > 1 will display all columns.
NOTE: It will work only with SQL SERVER 2005 and above.
Thanks,
Tejas
SQLYoga.com
In one table deleted 9 out of 10 in same record.Two fields are present in database name and rownumer.both are same records.eg Daya,1.
thanks for posting this mail this post help me well
Thanks a lot..
Hi,
This is my Query
Select DisplayTypeName, COUNT(DisplayTypeName) As TotNoOfDisplay FROM ProjectImplementation_Info GROUP BY DisplayTypeName HAVING (COUNT(DisplayTypeName) > 1)
My problem is I am unable to find sum of TotNoOfDisplay.
Anybody help me please.
Any response would be much appreciated.
@joyson
Change COUNT(DisplayTypeName) to COUNT(*)
Its very good solution…..
Hi,
I want to find duplicate records from table which has FirstName and LastName Fields(Same name).
My query wants to pull out all duplicate records(Same first name or last name) from the table. Also I want the ability to also pull names where there might be a middle initial placed in the end of the first name field, (i.e., “Maria Z.” vs. “Maria”) as well.
Please guide me..
Thanks in advance
Prem
1 Duplicate in both names
select columns from table
where firstname=lastname
2 Find firstname with middle name
select columns from table
where firstname like ‘% %’
Hi,
I want to delete every duplicated row and keep one of them.
I have something like this:
Number status
460 PRE
460 POS
467 PRE
466 PRE
467 POS
466 POS
468 PRE
470 POS
468 POS
470 PRE
I need something like :
Number status
460 PRE
467 PRE
466 PRE
468 PRE
470 PRE
any help is apreciated!
Hi Pinal,
I am new to SQl Server 2008.
Could you give me a link to learn about SQL and writing queries n T-SQL.
Thanks
Kiran
Hi all,
I have two tables one of the table columns like
tbl_emp
Id Total stops Total Emp
100816080630010P 5 6
tbl_Details
Id Stopnumbers EmpId
100816080630010P 1 BACS136
100816080630010P 3 EmpHydTest118
100816080630010P 2 EmpHydTest13
100816080630010P 2 EmpHydTest19
100816080630010P 4 EmpHydTest52
100816080630010P 1 hdtest177
Form above tbl_trips total employees are 6 and in the tbl_tripdet empid count is 6 and stopnumbers should not repeat like this and employees count should be same. If the count is less than or grater than then those details should be get. It should be like 1,2,3,4,5,6. If the case is like as show above stopnumbers repeating then we should get those records.
I tried like this
SELECT fld_tripid FROM tbl_trips tb
WHERE (fld_totalemps != (SELECT COUNT(fld_EMPID ) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid) OR
((tb.fld_TotalEmps * (tb.fld_TotalEmps+1))/2) !=
(SELECT SUM(fld_StopNumber) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid))
AND (SELECT COUNT(fld_EMPID) from tbl_TripDet where fld_TripID = tb.fld_TripID) >0
When i used this query it’s taking time to run.
Thanks & Regards,
Jeevan.
I’ve been following your blog closely. Well done and thanks for your help.
I have a couple of queries:
1. I’m trying to write a sql statement that works out staff with more than 1 year service for 2009 and 2010 respectively.
2. Total number of staff in post in 2009 and 2010 respectively
3. Given financial years: 2005, 2006, 2007, 2008, 2009, Present:
a. find the Average number of Staff in the period
b. number of leavers.
See sample code but returning wrong values and duplicate records
SET DATEFORMAT YMD
SELECT distinct known_as_and_surname
,p.start_date
— ,c.full_time_equivalent as FTE1
,(c.career_date) as career_date
,p.continuous_start_date
,c.career_end_date
,p.termination_date
,year(p.termination_date) as termination_year
,count(distinct c.parent_identifier ) as nstaff
,year(c.career_date) as iyear, ltrim(year(c.career_date)) + ‘/’ + ltrim(year(c.career_date)+1) as year_text
,datediff(year, p.continuous_start_date, termination_date) as termination_no_years
,datediff(year, career_date, getdate())-365 as lessthanyr2009
,(datediff(year, career_date, getdate())) as lessthanyrnow
,CASE WHEN (datediff(year, career_date, getdate())) > 1 AND (termination_date <= getdate()-365) OR (career_end_date 1 THEN 1 ELSE 0 END as yr2009
FROM PWA_MASTER.people AS p INNER JOIN
PWA_MASTER.career AS c ON c.parent_identifier = p.unique_identifier INNER JOIN
PWA_MASTER.team AS u ON c.team_reference = u.team_reference
INNER JOIN
(Select distinct parent_identifier, year(career_date)-1 as fyear
FROM PWA_MASTER.career
where career_date < getdate()
group by parent_identifier, career_date)as c1 on c.parent_identifier = c1.parent_identifier
WHERE (c.division_reference ‘TSTD’)
GROUP BY c.parent_identifier, known_as_and_surname, c.career_date, c.career_end_date, p.start_date, p.continuous_start_date, termination_date, c.parent_identifier
ORDER BY c.parent_identifier
Thanks for all your help
Great query
thanks
Hi Pinal,
Need your help.
I’m trying add new column (status) to my office existing database. Each time they enter the new transaction to DB, i need count number of that transaction (for that person) and fill the column with specific value.
Example : Current number of User_A transaction = 5
Then fill the value 01 in status column.
Rules,
IF, number of Transaction values are ODD then fill with 00
IF, number of Transaction values are EVEN then fill with 01
This column will be fill up with daily basis.
Thanks
Hint
If col%2=0 then ‘even’ else ‘odd’
Thanks a ton guys :)
Nice answer buddy,
ID_No Status Class Total_HigherClass
1001 Complete Higher
1002 Complete Higher
1005 Incomplete Middle
1100 Incomplete Low
1101 Complete Higher
1105 Complete Middle
I want Sum of tatal higher class beetween 1001 to 1005 where status = complete.
Answer is 2 here send me SQL Query to get that.
Please response to me for my question.
Hi.
I have 3 coloums which have three coloums
Amount,Description,Code id
All fields can contain duplicate record.
I want to delete all duplicate records as well as matching duplicate records.
Ex.
Amount ,Description Code id
100.00 A/R-Miscellaneous (Corporate) 31
100.00 Gasoline 231
200.00 Cash – Superior 5
-200.00 Cash – Superior 5
I want to only record Like this.
Amount ,Description Code id
100.00 A/R-Miscellaneous (Corporate) 31
100.00 Gasoline 231
Thanks in Advance
select t1.* from table as t1 inner join
(
select abs(amount) as amount,description,codeid from table
group by abs(amount) as amount,description,codeid
having count(*)>1
) as t2
on t1.amount=t2.amount and t1.description=t2.description and t1.codeid=t2.codeid
hi pinal sir,
Thnq for ur great query
it helps me a lot,,,,,,,,,,,
Hi
Can you advice How to take reports from sql server 2005?
and reply is there any 3rd party tool is avble from which we can execute reports..
thanks
Kasi
u r awesum man….
great work man
hats offff
thaks for the code, you saved my life!!