SQL SERVER – Count Duplicate Records – Rows

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

About these ads

178 thoughts on “SQL SERVER – Count Duplicate Records – Rows

  1. 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

  2. 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

  3. @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

  4. 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.

  5. 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

  6. 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.

  7. Pingback: SQL SERVER - Delete Duplicate Records - Count Duplicate Records Links Journey to SQL Authority with Pinal Dave

  8. 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.

  9. Hi Pinal,

    This is really too good solution to find and delete duplicate records from database table.

    Thanks ,
    Pravin

  10. 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.

  11. 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

  12. 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.

  13. 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

  14. 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

  15. 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

  16. 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

  17. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  18. 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. :(

  19. 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

  20. 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

  21. 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 …

  22. 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)

  23. 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!

  24. @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);

  25. 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?

  26. @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.

  27. 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.

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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.

  35. 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.

  36. 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 ‘% %’

  37. 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

  38. 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.

  39. 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

  40. 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

  41. 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.

  42. 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

  43. 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

  44. 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.

  45. 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

  46. Hi,

    How can i get the particular character location/position from table column.

    ex. select * from TABLENAME Functionname(table_column,’?’)

  47. 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

  48. 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.

  49. 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?

      • 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?

  50. 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

  51. 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)

  52. 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′

  53. 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…

  54. 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?

  55. 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

  56. 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

  57. 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?

  58. 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.

  59. 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)

  60. 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’)

  61. 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.

  62. 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.

  63. Hi Pinal,

    Can you help me by providing the solution to my above query. It is some what urgent. Please…

  64. 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)

  65. Mr. Pinal,

    Request you to take some time from your busy schedule to provide the guidelines (Solution) for my query above.

    Thanks

  66. 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

  67. 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

  68. 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.

  69. Pingback: SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video « SQL Server Journey with SQL Authority

  70. 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.

  71. 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

    • Hi – I’m not sure if you are still trying to accomplish this. However, you are getting duplicates because Dorababu joins to two rows in Address. This is because his primary key on Employee links to both the first and second row of Address (“1″).

    • If that is a typo on row 2 of Employee then this will work (if it is not a typo please give more specific instructions regarding the sample data and expected output):

      IF OBJECT_ID(‘tempdb..#Employee’) IS NOT NULL DROP TABLE #Employee
      IF OBJECT_ID(‘tempdb..#Address’) IS NOT NULL DROP TABLE #Address

      CREATE TABLE #Employee
      (
      ID VARCHAR(25) NOT NULL,
      Name VARCHAR(25) NOT NULL
      )

      INSERT INTO #Employee(ID, Name) VALUES
      (‘1′, ‘Dorababu’),
      (‘2′, ‘Vivekh’),
      (‘3′, ‘Kaashyap’)

      CREATE TABLE #Address
      (
      F_ID VARCHAR(25) NOT NULL,
      Address VARCHAR(50) NOT NULL
      )

      INSERT INTO #Address(F_ID, Address) VALUES
      (‘1′, ‘XYZ’),
      (‘2′, ‘XYZ’),
      (‘3′, ‘ABC’)

      –The actual query starts here
      SELECT e2.Name, s.Address
      FROM
      (
      SELECT COUNT(*) AS Dups, a.Address
      FROM #Employee e JOIN #Address a ON e.ID = a.F_ID
      GROUP BY a.Address
      HAVING COUNT(*) > 1
      )s JOIN #Address a2 ON s.Address = a2.Address
      JOIN #Employee e2 ON a2.F_ID = e2.ID
      –The query ends here

      DROP TABLE #Address
      DROP TABLE #Employee

    • I decided to try a different method. This one achieves the same results with less joins:

      SELECT e.Name, a.Address
      FROM #Address a JOIN #Employee e ON a.F_ID = e.ID
      WHERE a.Address IN
      (SELECT a2.Address FROM #Address a2 GROUP BY a2.Address HAVING COUNT(a2.Address) > 1)

  72. 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

  73. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

    • Thanks Deepak, I just added CTE to compare against

      WITH
      table1 (ID, value) AS
      (SELECT 1 , ‘ID1′
      union all SELECT 2, ‘ID3′),
      table2 (ID, value, meta) AS
      (SELECT 1, ‘ID1′, ‘?’
      union all SELECT 3,’ID2′, ‘”‘)

      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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s