CODEWITHSUNDEEP
sql
nav100
nav100

Reputation: 1487

SQL query with duplicate records

I am trying to write a query in SQL server to find out if there are any multiple rows for each customer 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

Upvotes: 6

Views: 83231

Answers (3)

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

To see how many of each customer you have:

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

You can use a having clause to limit to just duplicates:

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

UPDATE

To get those with 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;

Upvotes: 5

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

You can use a GROUP BY query to achieve this:

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

Upvotes: 17

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 0

Related Questions