Menu
If I have data like this:
| Key | Name |
|---|---|
| 1 | Dan |
| 2 | Tom |
| 3 | Jon |
| 4 | Tom |
| 5 | Sam |
| 6 | Dan |
What is the SQL query to bring back the records where Name is repeated 2 or more times?
So the result I would want is
| Tom |
|---|
| Dan |
307
We can use GROUP BY to group together rows that have the same value in the Animal column, while using COUNT() to find out how many ID's we have in each group. It returns a table with three rows (one for each distinct animal).
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
Here is the basic syntax: SELECT COUNT(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table. COUNT(column_name) will not include NULL values as part of the count.
Couldn't be simpler...
Select Name, Count(Name) As Count From Table Group By Name Having Count(Name) > 1 Order By Count(Name) Desc This could also be extended to delete duplicates:
Delete From Table Where Key In ( Select Max(Key) From Table Group By Name Having Count(Name) > 1 ) 
select name from table group by name having count(name) > 1 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
