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

2 Answers

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.

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.

1