mysql count duplicates

Question

how can I count duplicates rows wihtin select statement

here is my table

name food  
A    Apple  
B    Banana  
A    Banana  
C    Apple  

Result should be like this:

name food count
A    apple (2)  
B    Banana (1)  
A    Bananaa (2)  
C    Apple (1)  

I need a third coloumn in result set which value will be count (Name)

Thanks in advance

Answer 1

It's unclear exactly what you want to do, so here's two possibilities.

If you want to determine how many times the same name and food combination occurs, you can use GROUP BY to group like records and COUNT to determine how many there are in the group:

SELECT name, food, COUNT(*) AS count
FROM your_table_name
GROUP BY name, food

Alternately, if you want to retrieve how many times only the name duplicates, you'll need a subquery:

SELECT name, food,
  (
    SELECT COUNT(*)
    FROM your_table_name
    WHERE name = table_alias.name
  ) AS count
FROM your_table_name AS table_alias

The first query will return only one row per name/food group, along with the count of how many records appear in the group. The second query will return all rows, with a count of how many rows have the same name in each row.

Answer 2

SELECT name, food, COUNT(food) AS cnt
FROM table
GROUP BY name, food
HAVING (cnt > 1)

If you want the count of everything, not just the tuples with duplicates, then eliminate the HAVING line, as that'll filter out anything that doesn't have duplicates.