SQL query with duplicate records

Question

I am trying to write a query on a SQL server to find out if there are several rows for each client by customerID. Please let me know.

Here is the table structure

Customer table ----------------------------------------- orderID CustName CustomerID --------------------------------------- 100 test 123456 101 test 123456 Orders table ------------------------------------ pID OrderID ----------------------------------- 1 100 2 101 

Answer 1

You can use the GROUP BY query to achieve this:

 select CustomerID, count(*) as NumDuplicates from Customer group by CustomerID having count(*) > 1 

Answer 2

To find out how many each client you have:

 SELECT COUNT(*), CustName, CustomerID from Customer Group by CustName, CustomerID 

You can use the having to restrict only duplicates:

 SELECT COUNT(*), CustName, CustomerID from Customer Group by CustName, CustomerID having count(*) > 1 

UPDATE

To receive successful orders:

 select count(*), CustName, CustomerID from( SELECT CustName, CustomerID from Customer, orders where customer.orderID = orders.orderID and orders.success = 1) subquery group by subquery.CustName, subquery.CustomerID having count(*) > 1; 

Answer 3

 select CustomerID, count(1) from Customer group by CustomerID having count(1) > 1