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
First, use the GROUP BY clause to group all rows by the target column, which is the column that you want to check duplicate. Then, use the COUNT() function in the HAVING clause to check if any group have more than 1 element. These groups are duplicate.
MySQL COUNT() Function The COUNT() function returns the number of records returned by a select query.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With