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
Thank you for the duplicate sql query you supplied. It was almost exactly what I was looking for.
regarding duplicate records:
i want to fetch only duplicate record from a table. suppose in a table there is 2000 records , out of which 10 records are duplicate. so how we can get only 10 duplicate records.
select col from table
group by col
having count(*)>1
Hi here,
Apologies if this has been asked before, but I’ve not been able to see it. The query at the top to count duplicate records and order them in descending order is great, however, this appears to only work with fields from other tables.
I have set up an expression that combines a few fields together (eg concatenating surname, forname and DOB from a table into a new field in the query and called it “Fuzzy_ID”).
I wish to find duplicates of the “Fuzzy_ID”, but get errors regarding “Fuzzy_ID” not being an identifier in the GROUP BY line and can’t get the HAVING COUNT(*) to work either.
Must I create two queries? IE would there be one query (QUERY_A) to create FUZZY_ID field and then create a second query (QUERY_B) to use the SQL above basing the count on FUZZY_ID from QUERY_A?
Many thanks
Hi,
How can i get the particular character location/position from table column.
ex. select * from TABLENAME Functionname(table_column,’?’)
Use charindex function
charindex(‘?’,col)
Thank you
hi ,
in my query i got 2 rows .how to count the row from this select statement.
my selected query is ds
select PSCInspectionID,SUM(ISMDeficiencies + NonISMDeficiencies ) from tPSCInspection
join mPSCMoU on mPSCMoU.PSCMoUID =tPSCInspection.PSCMoUID where mPSCMoU.IsParisMoU=1
group by PSCInspectionID having SUM(ISMDeficiencies + NonISMDeficiencies )>=5
the out put is
i got two rows
if i run ds query to count the row
SELECT COUNT(*) as total_duplicates FROM
(
select PSCInspectionID,SUM(ISMDeficiencies + NonISMDeficiencies ) from tPSCInspection
join mPSCMoU on mPSCMoU.PSCMoUID =tPSCInspection.PSCMoUID where mPSCMoU.IsParisMoU=1
group by PSCInspectionID having SUM(ISMDeficiencies + NonISMDeficiencies )>=5
) as t
i got an error
like ds
Msg 8155, Level 16, State 2, Line 15
No column name was specified for column 2 of ‘t’.
wht is wrong in my query
can u pls help me
Hi,
IN SQL 2005 – Some Stored procedures have “modify” enabled some are having “modify” disabled on Right click.
while checking the properties of that StoredProcedures
in Options “encrypted = True”
How to change cncrypted=FALSE.
Thanks has helped alot
Thank you. I’d forgotten how to do this and your post pushed me back in the right direction.
I have a question, could anybody guide me to have a WHILE … LOOP query to run the duplication check on first two columns of all tables in a specified database?
Why do you want to use while loop? What is wrong with the example given in this post? Using While loop is ineffecient
I am into an upgrade process of my software application, and it gives me error of duplicate records found in tables. My database contains more than 1000 tables and I’m unable to execute the query on each table manually. Is there any possibility that a single query can check all tables in the whole database for duplication?
Duplicate on what basis? Are the columns fixed?
How can I get a count query result for the following email table where direction column contains 1 for incoming and 2 for outgoing :
id | email data | direction
1 | abcded |1
1 | sdfdfdf |2
1 | asdadsf |1
1 | dfdfdf |2
I want the out put as :Email Report:
id | incoming | outgoing
1 | 2 | 2
Can any one please advise me…Thanks a lot in advance.
God Bless you all for a happy,peaceful and non violent , humanly world
select id, sum(case when direction=1 then 1 else 0 end) as incoming,sum(case when direction=2 then 1 else 0 end) as incoming from table
group by id
Hello Madhivanan,
Thanks a lot and all the best wishes…Regards, Suresh Kumar VC
how can i sum alternate rows in table
Example: 12 AM
3AM
6AM
10AM
1st record is IN Time
2nd record is out Time
now i need to sum the alternate rows and this the time where a Employee comes in and goes out so i need to get his working hrs
Now result should be as below
3 HRs (12 to 3 AM)
4 hrs (6 to 10 AM)
i have one table empid. day1,day2,day3,da4; and its value is e001,pp,pp,pp,aa…….
now iwant to know how many columns having value pp of empid e001………….plz tell me fast…
select empid,case when day1=’pp’ then 1 end +case when day2=’pp’ then 1 end +case when day3=’pp’ then 1 end +case when day4=’pp’ then 1 end +case when day5=’pp’ then 1 end from table where empid=’e001′
RESULT SHOULD BE 3…..TELL ME
Hello sir … 100 rows in a table i want split first 25 and second 25 rows only … 25 wise
how do please help me sir…
You need pagination. Refer method 4 here
sir .. i have 2 variables … a and b … a=25, b=148, and divide b/a=c,
this balance 0 then print c value otherwise balance available then add 1 to c ..
c+=1;
how to write query please help me sir…
you need to use
select a*1.0/b
For more informations refer this
Hi,
I’m looking for a similar but different query that I’ve been twisting my brain trying to figure out. Your post is the closest I’ve come to finding an answer.
I’m querying a table where multiple people might be selling the same item (ItemID). What I want to do is get all unique items, and for each unique item, get a count of how many duplicates there are for that unique item. I want a list of all unique items, not just the ones that have duplicates. (For ones with no duplicates, the duplicate count can can be one or zero, I don’t care.)
A simplified version of the ForSale table is:
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Selling_ID] DEFAULT (newsequentialid()), — This is the primary key
[ItemID] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](max) NULL
There is a second table “Items” that contains the information about the item in question. I’m trying to link the output of the
I’ve gotten almost all the way with your above query:
SELECT ItemID, Count(1) TotalCount
FROM ForSale
Group BY ItemID
HAVING COUNT(1) > 0
ORDER BY COUNT(1) DESC
But I can’t figure out how to get the Item info also brought into the results of the query. I thought an inner join would do the trick, but then I end up with a record for each ForSale, defeating the unique.
Any ideas?
Thanks. Just as exactly what I am looking for. It worked as expected.
thanx….
hi, thank you, very well.
Hi pinal ,
I have some problem regarding sqlserver which is that i have db in which i have to put condition like user can save five time data and when they do six time it will not add and mention the error,.
Thanx
Waqar Isamil