[SOLVED] duplicate record X 2

[XpertWorlock]

What I want to do is go through a list of names (in one table column) and when it hits the same name twice it saves it as "x2" or something and at the end it would output Jeff x 2.

 

So if you had 4 records (Amy, Jeff, Jeff, Stan) at the end it would output

 

Amy

Jeff x 2

Stan

 

Can't think of a proper (non messy) way of doing this.

 

[xtopolis]

This is an expensive query (I think), but it will show you the counts:

 

SELECT DISTINCT(name), COUNT(name) as 'Amt' FROM `test` GROUP BY name

and if you want to filter it to show only ones with dupes add:

HAVING Amt > 2

 

Not sure if it is case sensitive or not.

[XpertWorlock]

thanks, yeah count function in mysql works great!

[fenway]

Not expensive with a proper index... you don't need distinct, group by will take care of that.  And note that not using '*' in the count() treats NULLs different.