Removing duplicates while counting records

• Kerstin Safari

  Right there with Babe

  Points: 754

  More actions

  May 24, 2007 at 11:11 am

  #173811

  This might be very easy, but I am having a brain cramp on this:

  I have one table that only has one field in it, DIVISION, The DIVISION is two character field.

  There are probable duplicates of DIVISION, What I need to do is at the same time as I count how many records there WERE for each DIVISION, I need to delete the duplicate records from my

  table. Does anyone know how to do this?

  For instance:

  DIVISION COUNT

  -------- -----

  01 1

  01 2

  01 3

  02 1

  02 2

  02 3

  02 4

  COUNTS

  -----

  DIVISION 01 = 3

  DIVISION 02 = 4

  Thanks in advance for any help with this.

• Belisama

  SSC Enthusiast

  Points: 188

  More actions

  May 24, 2007 at 12:03 pm

  #708742

  The only thing I've been able to come up with is this, but I imagine there's a better way. Plus, I'm not absolutely certain this is what you want.

  em

  create table #cnts(Division varchar(2), iCnt int)

  insert into #cnts

  SELECT Division, COUNT(Division) AS NumOccurrences

  FROM <your table>

  GROUP BY Division

  HAVING ( COUNT(Division) > 1 )

  truncate <your table>

  insert into <your table> select Division, iCnt from #cnt

   

• Kerstin Safari

  Right there with Babe

  Points: 754

  More actions

  May 24, 2007 at 12:40 pm

  #708752

  I actually was fiddling with this, and came up with:

  select DIVISION, (COUNT(DIVISION)) as DIVCOUNT

  into #D1A

  from #DIVISIONTABLE

  group by DIVISION having COUNT(*) > 0

  And it worked!

  It looks like your solution would work, too, in much the same way.

  Thanks for the quick response. (I love this forum for that reason, and the fact that the responses usually work, too)