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:
Reference : Pinal Dave (http://blog.SQLAuthority.com) , SQL SERVER – Delete Duplicate Records – Rows
its a good solution so far …
thanks
Good
Great solution, many thanks for this.
Dear Pinal,
I visited your blog so manytimes and get the solution. Thank you so much for keep updating your blog.
I have problem and i wanted you to give some time to resolve and given me a solution.
I have one table containing the records like this
Movie ID Movie Name New Name
1 Spider Man Spider Man
2 Spider Man 2 Spider Man
3 Spider Man 3 Spider Man
4 Spider Man UK Spider Man
5 Spider Man USA Spider Man
6 New Spider Man Spider Man
7 Spider Man Black Spider Man
8 Spider Man Part 1 Spider Man
9 Spider Man Part 2 Spider Man
10 Spider Man I Spider Man
11 Spider Man III Spider Man
12 Spider Man Part II Spider Man
My manufacturer send me the data in this format and i have to allocate there new name to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a duplicate records and also the suggestion for the new name.
I am fully confident that you will help me out from this problem.
Looking forward
Rana
Hi Pinal.
I have same problem as Rana.
Please try to solve it. I really need a solution.
Thanks in advance.
select day, count(*) from test GROUP BY day;
USE IT
select day, count(*) from test GROUP BY day;
EASY AND LESS COMPLICATED ANS
Dear pinal,
any luck with the solutions.
Rana,
Not yet, I many not have time to write custom solution in near future. I will look into it soon.
Regards,
Pinal
Please find some time to write custom solution.
I hope you understand my problem and how to resolve it.
Good Luck.
Looking forward on this weekend.
Rana
Dude rana, why dont you find your own solution instead of scamming people who actually know how to write sql.
@Dominator, @Rana,
Please calm down.
Rana, as I previously said, I have responsibility of running a large company and I have very little time.
I write SQL to help most of the people. Your request is very much specific and with restricted need and I do not see how it can be helpful to many readers.
I will be not able to comply your request. You will have to help yourself. Again, I am sorry, as you ordered I am not able to find sometime in weekend.
Good luck,
Regards,
Pinal Dave
Dear Pinal and Vistor Dominator,
I wrote here because i was wondering that you must have done something before so i can use that.
but no worries i have spent some time and get the solutions.
Few algo i wrote.
some counts and its really a wonderful solutions for even cleanign the data.
I will be posting this in this weekend.
I need to explain line by line otherwise people might not udnerstand.
thanks anyway.
Great tip; exactly what I was looking for. Thanks for posting it.
I want the show a result that choose the line that has the lowest price
What do you suggest
Table
Item Code Price
Spider $10.00
Spider $9.00
Ant $5.00
Ant $4.00
The result I want to see would be
Spider $9.00
And $4.00
Thanks
select spider,min(price) as price from table
group by spider
Thanks your solution was exactly what I was looking for. I had a bunch of ID’s with a different times of a code, that I need to know how many occurrences for each individual. Your code gave all that, and allow me to get the data to program accordingly.
It is great article. It real useful to me for quick result. Thank you, keep post this kind of articles.
[...] SQL SERVER – Count Duplicate Records – Rows [...]
Thank you, your solution worth 5 marks for my coursework. ;-)
THis works for me very nice!
Thnx!
Hi Pinal,
Thanks for the great info. I couldn’t believe how easy it is to accomplish what I was trying to do. I took what you wrote and modified it a bit since I only needed to know how many of each duplicate I had.
Thanks realy imagine
For 11. Alex, you could do this perhaps:
SELECT
ITEM_CODE,
MIN(PRICE) AS PRICE
FROM TABLE
GROUP BY ITEM_CODE
ORDER BY ITEM_CODE
Hi Pinal,
This is really too good solution to find and delete duplicate records from database table.
Thanks ,
Pravin
Hi Pinal,
One more soultion to Delete completely similar record from database table
We can play with row Id of the table row to delete a completely similar records , keeping one avilable using sysobjects in sql server 2005.
Thanks,
Pravin.
Thanks a lot.
select sales6.Sou_Enqu,(count(sales5.project_reg)),(count(sales5.project_EMRB))from sales6,sales5 where sales5.sales_no=sales6.sales_no and sales5.project_reg =’Regent Park’and sales5.project_EMRB=’ECB’ Group by sales6.Sou_Enqu;
this is my query i want count 10 field value in that query both field count value is displaed same.
source regent park ecb eck
newpeper 10 20 30
email 20 10 20
like that format
plz help me soon
tell me solution
Another solution is to put the table with duplicates in join with itself.
For a simple example see:
http://www.ugmfree.it/TipsTsql.aspx?tip=TipTsqlDeleteDuplicates
thx pinal…. it was very easy …
Pinal,
The query posted at the top of this page results with duplicate column and its count in descending order.
How can i find the count the returned rows???
Please help me.
Try this
SELECT COUNT(*) as total_duplicates FROM
(
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
) as t
Hi Pinal,
I have a question.
I need to loop through a table and if there are some duplicate values in one of the columns I need to add a suffix of a, b, c, and so on. But, there are different sets of duplicates. So, I need to do the suffix on each separate set of duplicates.
Please notice bellow, there are three sets of values that are duplicates. This is a sample of my data.
Could you give me any ideas on how to accomplish this task? Thank you.
ZP19188AMR
ZP19188AVE
ZP19188AVH
ZP19188AVH
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188COC
ZP19188PHM
ZP19188PHM
ZP19188RIM
ZP19188SAB
ZP19188WEH
select col, count(col) from table
group by col
having count(col)>1
thanx.. its short and effective… worked for me..
thanks for the post it really helped me.
Thanks. This is very helpful.
I need to limit the number of duplicates. Can this be achieved? Thanks.
Use that limit in place of N in the following
Having count(*)>N
Thanks.
HI Pinal,
If I want to select all the fields in a table,
How shold I write the query for that
thank u
select t1.* from table as t1 inner join
(
select col from table group by col having count(col)>1
) as t2
on t1.col=t2.col
thanks.
select Taluk_Name_Eng ,count(Taluk_Name_Eng)as kk from tbl_PL04_Taluk
what problem in this query
You need to include that column in the group by clause
select Taluk_Name_Eng ,count(Taluk_Name_Eng)as kk from tbl_PL04_Taluk group by Taluk_Name_Eng
Using the above Query is nearly just what I need but it only gives me an ID (YourColumn). I have another table which would have a named value for this ID. How can I return that instead? A Join or a nested statement?
Many thanks
Did it, quite new to SQL I’m afriad, hope it helps another novice :)
SELECT top 20 computers.name as CID, COUNT(*) as ErrorCount
FROM errors
inner join computers
on computers.id = errors.computerid
GROUP BY computers.name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Hi,
I have written to get Duplicate Rows from the table. You can use the ROW_Number() in SQL 2005.
To read more about it:
http://tejasnshah.wordpress.com/2008/12/31/find-duplicate-rows-with-row_number-sql-server-2005/
Tejas
u r good
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
this query is solved my lot of problems
Thank you very much
[...] SQL SERVER – Count Duplicate Records – Rows [...]
If you’d like to get a total count of the duplicates as I needed to do, try returning the above query as a derived table and then do a sum on the count.
SELECT SUM(Duplicates.TotalCount) FROM (SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1) AS Duplicates
My database has over 1200 duplicates. :(
Hey Pinal,
Excellent query ! thanks !
its really good update its help ful for every one thanks
i have a data entry system for patient records. in my system there are several forms through which data is entered in different tables. and there is a form which takes the present condition of the patient (i call it PresentAssessment). now i what i want is when the user enters a new record i want to compare the values of the attributes (database table fields) of the newly entered patient and from all the matching records i want to calculate and return the chance(in terms of %age) of particular disease(s) (stored in PresentAssessment) to newly added patient. e.g. if i have two tables i.e. PatientDrugHistory and PresentAssessment. and say each of them already have 10 records with 10 columns each. now when entering the 11th record in PatientDrugHistory i want to calculte and store how much chances (in terms of %age) exist that this 11th patient is suffereing from disease1 (a column in PresentAssessment). please if anybody can tell me what query to write for this purpose
This one is a great help however I’ve tried retrieving other fields besides the one I’m counting with duplicate record. How do I do that? This is my script. I can’t seem to include the claim_no, lastname and firstname field.
SELECT bank_acct, COUNT(*)
FROM july09_master_copy
WHERE cl_status = ‘z’
AND rid_ind = ‘r’
GROUP BY bank_acct
HAVING COUNT(*) >1
ORDER BY COUNT(*)
SELECT t1.bank_acct,t1.claim_no,t1.lastname from july09_master_copy as t1 inner join
(
SELECT bank_acct, COUNT(*)
FROM july09_master_copy
WHERE cl_status = ‘z’
AND rid_ind = ‘r’
GROUP BY bank_acct
HAVING COUNT(*) >1
) as t2 on t1.bank_acct=t2.bank_acct
hi all
can you help me, I want to find transaction with the same minutes on the table transactions
example case : I have 2 user which has 3 time transaction on the same minutes of the day on my Transaction table
can you give me the example script please …send it my email : ifi.lutfi@pln.co.id
Thanks …
Hi lutfi
may be this will help you
select user_name,transaction_id, transaction_time from transactions where transaction_time in (select transaction_time from transactions group by transaction_time having count(transaction_time) > 1)
Good solution, many thanks for this.
Thank you Pinal & udai,
great help in increasing my sql knowledge :).
My Statement currently looks like this:
SELECT
FIELD15
FROM CUSTOM
HAVING COUNT (*)>1
GROUP BY FIELD15
However I’m wanting to add another column in this statement so I’m able to see the account numbers associated with Field15
Please help!
Thank you!
@Nicole
So, you want account numbers for any group of Field15 that has more than one member?
SELECT Field15, Account FROM Custom WHERE EXISTS(SELECT * FROM Custom B WHERE B.Field15 = Custom.Field15 GROUP BY Field15 HAVING COUNT(*) > 1);
Brian-
Thank you so much for helping me! That worked, but didn’t provide the information I’m needing.
I’ll explain what this report is currently doing and what I need it to do. It currently pulls all duplicated secondary account numbers (field15) off of the primary account (account) from the report (custom). What I need it to do is provide the primary account number to where the secondary account number is being duplicated (for example I’ll have 2 different primary accounts numbers but the same secondary account number associated with the primary accounts) so I know what account how the error. It’s harder to explain something when somebody has no clue what your talking about :), I really appreciate the help!
Does that make sense?
@Nicole
I don’t understand
The query i posted will grab any information wwhere Field15 is duplicated. If you want another COLUMN, why not just add it:
SELECT Primary_Account, Field15, Account FROM Custom WHERE EXISTS(SELECT * FROM Custom B WHERE B.Field15 = Custom.Field15 GROUP BY Field15 HAVING COUNT(*) > 1);
Perhaps if you posted the TABLE structure, a small amount of data (3 records should be fine, 1 duplicated (making a total of 2), one not) and what results you expect.
I was having a dedede moment, it works! Thank you!
Great solution………………………
Thanks
Hello Mr. SQL Sir,
I want to find alternate records in sql server.
Can u please suggest me a way for this.
Hi Manjeet,
I am not clear about your requirement of alternate records.
Regards,
Pinal Dave
The requirement is i want to retrieve records from alternate rows like 1st,3rd,5th,7th and so on….. from a table.
How can i do it???
I tried googling on it but that is showing the use of mod() function which is not supported in sql server 2005.
Hello Manjeet,
Following is the script to get alternate TableCol1 rows from table TableTest:
WITH CTE (RN, TableCol1)
AS
(SELECT ROW_NUMBER() OVER (ORDER BY TableCol1), TableCol1 FROM TableTest)
SELECT TableCol1 FROM CTE WHERE RN%2 = 1
Regards,
Pinal Dave
Hi Pinal bro,
i have a problem
i want to delete duplicate rows
except one row on duplicate rows
that will not be deleted.
i mean to say i have record like this :
ID : 1 Name : fawad
ID : 2 Name : ashfaq (this should not be deleted)
ID : 2 Name : Pinal (this should be deleted)
ID : 2 Name : Dave (this should be deleted)
ID : 3 Name : Manooj
please help me
Thanks
Fawad ashfaq
@Fawad
look at the answer Pinal gave to Manjeet, and change: RN%2 = 1 to RN > 1
Hello Fawad,
Use the ROW_NUMBER function as in below example:
WITH xTab AS
(
SELECT Column1, Column2,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS [Count]
FROM MyTable
)
DELETE FROM xTab WHERE [Count] > 1
Regards,
Pinal Dave
kalyan.
hello Pinal,
I want to retrieve the alternate records in a table.
Regards
kalyan
Hello Kalyan,
Alternate record on which column value? By position there is no alternate record. You can use the ROW_NUMBER function and modulo (%) operator to get alternate records.
Regards,
Pinal Dave
Hi pinal,
i have a question
if i want to retrieve the complete record(all colums values)?
i don’t know how to do this, as i can’t give all the column names in select clause, if i give so i have give the column names in GROUP BY clause which doe’s not give me the desired result,,
Waiting for your reply,
THANK YOU PINAL
@Shobha Reddy
Please give an example of the record and the desired results.
select t1.* from table as t1 inner join
(
your_query_that_finds_duplicates
) as t2 on t1.keycol=t2.keycol
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:
http://www.sqlyoga.com/2009/03/sql-server-find-duplicate-rows-with.html
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 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!!
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
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
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
SELECT EBD.sFirstName + ‘ ‘ + EBD.sMiddleName + ‘ ‘ + EBD.sLastName AS EmpName,
EBD.sFatherName ,
EOD.dJoinigDate,
–’Present’,'Weekly Off’,'Holiday’,'Half Day’
ISNULL( (SELECT SUM(CASE WHEN EA.D1=’Present’ THEN 1 ELSE CASE WHEN EA.D1=’Weekly Off’ THEN 2 ELSE CASE WHEN EA.D1=’Holiday’ THEN 3
ELSE CASE WHEN EA.D1=’Half Day’ THEN 1 ELSE 0 END)
FROM tbl_empAttendenceLog EA Where EA.iEmployeeID=EBD.iEmployeeID and EA.sMonth =’January’ Group by EA.iEmployeeID ),0) as Oneday
FROM tblEmployeeBasicDetail EBD INNER JOIN
tblEmpOfficialDetails EOD ON EBD.iEmployeeID = EOD.iEmployeeID INNER JOIN
tblClientBranch CB ON EOD.iBranchID = CB.iBranchID
please give a query to find duplicate email ids
in this format:
for eg:
[email removed]
this means it has repeated 4 times. i want both mail id and number
i want to count a recods in my SQL database as one item.
i code my as:
Select LabNo, MyAllNames, TestName, TestCost, MyMonth, TestDate, COUNT(TestName) As MyExam, (MyExam * TestCost) as TotalSale From TestSelectedByPatient Group by LabNo, MyAllNames, TestName, TestCost, MyMonth, TestDate
BUT THE ABOVE CODE GIVES ME AN ERROR SAYING THE COLUMN MYEXAM DOED NOT EXISTS. can you please help me?
Hi, please how do i run a vb6 setup using SQL as it’s database without installing package on the client’s/customers computer. Please kindly help.
Wow, lots of questions on this. Just goes to show that you can lead a horse to water, but you can’t make him drink.
hi,
i have data that contains item, subinventory, locator and quantity like:
saw warehouse box1 100
saw warehouse box2 100
saw warehouse box3 100
saw warehouse box4 100
saw workshop table1 20
saw workshop table2 20
screw warehouse box1 200
screw warehouse box2 200
i want to divide the quantity by locator count. I need the result like:
saw warehouse box1 25
saw warehouse box2 25
saw warehouse box3 25
saw warehouse box4 25
saw workshop table1 10
saw workshop table2 10
screw warehouse box1 100
screw warehouse box2 100
can u help me how the sql do it to all data above in a line statement ?
Thx.
select item, subinventory, locator, quantity/(count(*) over (partition by item)) from table
Most of the time, is not the problem to find a duplicate record (in a good designed database, you should NOT have a duplications).
Sometimes, you need to find out a given column is duplicated for a given other column: if you have different values for the same value
(mostly a foreign key).
In this case, you count a nested select:
SELECT ID, COUNT(field) AS duplicate
FROM (SELECT DISTINCT ID, field
FROM Table) s
GROUP BY ID
HAVING (COUNT(field) > 1)
sir, i want to delete all employee details except for two employee no in the database. I have tried this one but it delete all the data..
Delete from Employee_Profile where EmpNo NOT IN
(select EmpNo from Boss_Employee_Profile
where EmpNo =’21097′ , ’21097A’)
Good day everyone. I’m hoping that someone can help me with my issue. I have duplicate values that I am searching for and while I can easily search for and find those, I can’t figure out how to validate the first record while invalidating the others. So lets say I have the following fields:
Q_ID, Q_Status_CD, Q_MatchKey, Q_Fulfilled
I want to find all records with more than one matching Matchkey so I can use:
SELECT Q_ID, Q_Status_CD, Q_MatchKey, Q_Fulfilled_DT
FROM MyTable
WHERE Q_Fulfilled_DT Is NULL
Having Count(Q_Matchkey) > 1
This works fine for showing all duplicates. Where I am struggling is that I need to update the Q_FULFILLED_DT field on the FIRST entry with a date as well as set the Q_STATUS_CD = 1 (valid), then on all other records set the Q_STATUS_CD = 2 (in-valid). I found a work around to do this by first setting all duplicates to invalid, then finding the duplicates and by using MIN(Q_ID) to isolate the first record and then I reset the Status and Fulfilled date fields on that individual record.
This solution would be fine, except this is part of a weekly SQL job and it resets all duplicates each week and therefore places a duplicate order for that first record each week by changing the fulfilled date to the current week. I want to only update the first record when there isn’t one with a Fulfilled date already in the duplicate set. I’m not sure if this makes sense.
Hi,
The output of my query contains duplicate records (i.e. column1 value is similar but other column’s value different) as well as single record having column2 value blanks(-). now I want to select only one record out of duplicate records for which column2 values are present (eliminating records for which column2 is blanks(-) from duplicate records) and all other single records having column2 values blanks.
My query’s Present Output
column1 column2 column3
abc xyz pqr
abc – klm
ijk – uvw
plo – ujn
yhk ttg wea
yhk – erf
Expected Output
column1 column2 column3
abc xyz pqr
ijk – uvw
plo – ujn
yhk ttg wea
Can someone help me. Thanks in advance.
Try this
select column1,column2,column3 from
(
select *,row_number() over (partition by column1 order by column1) as sno from table
) as t
where sno=1
madhivanan,
Your solution worked perfectly for me with a minor tweak to meet my desired outcome. Thank you!
Hi Pinal,
Can you help me by providing the solution to my above query. It is some what urgent. Please…
Hi i have to required following solution for total no. of records
id Created date
12343 05/15/2012
12345 05/15/2012
12121 05/16/2012
12369 05/19/2012
12371 05/19/2012
12343 05/22/2012
12888 05/22/2012
12411 05/25/2012
12121 06/06/2012
12125 06/06/2012
Total Record Create Date
2 05/15/2012
1 05/16/2012
2 05/19/2012
1 05/22/2012 (Repeat ID 12343 so not count that id)
1 05/25/2012
1 06/06/2012 (Repeat ID 121121 so not count that id)
Mr. Pinal,
Request you to take some time from your busy schedule to provide the guidelines (Solution) for my query above.
Thanks
I have already replied to your question. Please check
I’ll just leave this here
SELECT Field1, Field2, MIN(Field3) AS Min, MAX(Field3) AS Max, COUNT(*) AS TotalCount
FROM (SELECT Field1, Field2, Field3
FROM Tablename
WHERE [Criteria here]
GROUP BY Field1, Field2, Field3) AS FilteredDetail
GROUP BY Field1, Field2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
thanks sir
Hi Guys,
I have a list of product widgets, and we get the same product from different factories. I want to know how many are duped accross which facttories
ProductName, FactoryName
Smith Widget, Factory1
Smith Widget, Factory2
Smith Widget, Factory3
Dave Widget, Factory1
Dave Widget, Factory4
Claire Widget, Factory1
Claire Widget, Factory2
Claire Widget, Factory3
Claire Widget, Factory4
A) What I want is a SQL statement that gives a list of those Widgets that are suppled from Factory1 and Factory3
So for Factory 1 and Factory 3
Product,
Smith Widget,
Claire Widget
B) What would be amazing is
Name, Duplicates
“Factory 1 & Factory 2″, 2 Dups
“Factory 2 & Factory 3″, 2 Dups
“Factory 1 & Factory 3″, 2 Dups
“Factory 1 & Factory 4″, 1 Dups
“Factory 2 & Factory 4″, 0 Dups
“Factory 3 & Factory 4″, 1 Dups
This solution would be fine, except this is part of a weekly SQL job and it resets all duplicates each week and therefore places a duplicate order for that first record each week by changing the fulfilled date to the current week. I want to only update the first record when there isn’t one with a Fulfilled date already in the duplicate set. I’m not sure if this makes sense.
thank you. !!!
Grazie
[...] SQL SERVER – Count Duplicate Records – Rows [...]
I want to find duplicates record on the basis of fields FirstName, MiddleName, LastName from my table. I have 40 Lakhs of records in table. So any optimise query to show result in less time in SQL Server 2008 and system Specification for Such large amount of data for searching purpose.
Hi I am having a small requirement i.e I am having an Employee and Address tables where Employee table holds ID,Name and Address holds ID(foreign key) and Address. My data is as follows
Employee Address
1 Dorababu 1 XYZ
2 Vivekh 1 XYZ
3 Kaashyap 2 ABC
I would like to display all the employees who is having same address, I tried using joins but I am getting duplicates can some one help me
Thank you Pinal Dave
Thanks Mr.Pinal Dave
Hi Pinal,
I want to fetch the records from table1 having primary key ID but 1 column value depends on the condition and condition is : if ID is available in another table then column value should be “ColumnValue EXIST” if Not in another table then “ColumnValue NOT EXIST”.
Means I want to append some text to that column based on condition.
Thanks,
Deepak
I have got 1 Solution
select ID,case
when (select distinct 1 from table2 where table1.ID=table2.ID)=1
then value+’ EXIST’
else
value+’ NOT EXIST’
end
as status
from table1
any better solution would be appreciable..
Thanks,
Deepak
Thank you