Query that shows count of matching columns

[86Stang]

I've got the following table structure:

 

ID 		int(10) auto_increment
Phrase	varchar(10)

 

What I need to do is output a list of all distinct phrases but when there is more than one of the same phrase put a (x) next to it where x is the count of that phrase.  In other words, it should look something like:

 

Cow

Dog (x3)

Chicken

Zebra (x2)

 

As always, any help is much appreciated.  Loving the new look of the forums too!  SMF for the win!!

[rajivgonsalves]

you can use count and group by with concat

[Maq]

Try something along of the lines of:

 

SELECT CONCAT(Phase, IF(count(*)>1,CONCAT(' x',count(*)),'')) AS new_name FROM [table_name] GROUP BY Phase;

[fenway]

But this kind of thing should never be done in the query itself.

 

Get the value, get the count, and have the script output them as you please.