Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql count duplicates [closed]

Tags:

mysql

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

like image 937
Novice Avatar asked Oct 14 '10 15:10

Novice


People also ask

How do I count repetitions in MySQL?

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.

What is count (*) in MySQL?

MySQL COUNT() Function The COUNT() function returns the number of records returned by a select query.


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.

like image 120
Marc B Avatar answered Nov 16 '22 00:11

Marc B


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.

like image 20
Daniel Vandersluis Avatar answered Nov 15 '22 23:11

Daniel Vandersluis