SQL- how to count duplicates with conditions-CodePudding

I have a table like below:

ID   Doc ID      Names
XX    97...      Name1
XY    97...      Name 2
XZ    98...      Name 1
...

Now I want to find ALL the duplicates that exist for a particular name, but only any of the duplicates within the names contains a Doc Id beginning with 97. A simple duplicate query for SQL isn't helping, please help.

expected input & output

CodePudding user response：

Consider:

SELECT Table1.*, Query1.CountOfDocID
FROM (SELECT Names, Count(DocID) AS CountOfDocID FROM Table1 GROUP BY Names) AS Query1 
INNER JOIN Table1 ON Query1.Names = Table1.Names
WHERE CountOfDocID>1 AND Table1.Names IN (SELECT Names FROM Table1 WHERE DocID LIKE "97*");

CodePudding user response：

SELECT First(tblEmployee.[empid]) AS [tblEmployee.EmployeeID], Count([tblEmployee.empid]) AS NumberOfDups
FROM tblEmployee
GROUP BY tblEmployee.[empid]
HAVING (((Count(tblEmployee.[empid]))>1));

https://www.wikihow.com/Find-Duplicates-Easily-in-Microsoft-Access