1

There are ton's of listings on how to find duplicate rows, and remove them, or list out the duplicates. In the masses of responses i've tried searching through on here those are the only responses i've found. I figured I would just put up my question since its been an hour and still no luck.

This is the example data I have

Table Name: Customers
_____________________________
ID   | CompanyName
--------------
1    | Joes
2    | Wendys
3    | Kellys
4    | Ricks
5    | Wendys
6    | Kellys
7    | Kellys

I need to be able to find all the duplicates in this table, then put the results into another table that lists what the company name is, and how many duplicates it found.

For example the above table I should have a new table that says something like

Table Name: CustomerTotals
_______________________________
ID   | CompanyName  |   Totals
-------------------------------
1    | Joes         |    1
2    | Wendys       |    2
3    | Kellys       |    3
4    | Ricks        |    1

-----EDIT Added after 2 responses, ran into another question------

Thanks for the responses! What about the opposite? say i only want to add items to a new table "UniqueCustomers" from the Customers table that doesn't exist in CustomerTotals table?

eqiz
  • 1,521
  • 5
  • 29
  • 51
  • 3
    Looking at the answers, it's not clear what the `ID` field is supposed to be. Is it supposed to be an identity, or the `MIN(id)` for each group? – Mark Byers Jul 10 '12 at 20:19

5 Answers5

5

Try this :

INSERT INTO CustomerTotals
(CompanyName, Totals)
SELECT CompanyName, COUNT(*)
FROM Customer
GROUP BY CompanyName

Use an identity column for the ID field.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Thanks! What about the opposite? say i only want to add items to a new table "UniqueCustomers" from the Customers table that doesn't exist in CustomerTotals table? – eqiz Jul 10 '12 at 20:46
0

for get the duplicates, you can do

Insert into CustomerTotals (Id, CompanyName, Totals)
    Select min(Id), CompanyName, count(*) From Customers
    group by CompanyName

there you got the results, conserving the minimun id for each company name(if you really need the first id from your original table, as I see in the results). If not, you can use an Identity Column

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
0

If you only want the duplicates to be inserted into the second table, use this:

INSERT INTO CustomerTotals
(CompanyName, Totals)
SELECT CompanyName, COUNT(*)
FROM Customer
GROUP BY CompanyName
HAVING Count(*) > 1
Joe Barone
  • 3,112
  • 3
  • 24
  • 20
0

The above examples are fine. But you should use count(1) instead of count(*) to improve performance. Read this question.

Community
  • 1
  • 1
pdav221
  • 11
  • 2
0

I think below script is simplest...

SELECT CompanyName, COUNT(*) AS Total
INTO #tempTable
FROM Customer
GROUP BY CompanyName 
HAVING Count(*) > 1
DaveyDaveDave
  • 9,821
  • 11
  • 64
  • 77
Anjan
  • 1