CODEWITHSUNDEEP
mysqlsql
Matt Mcdon
Matt Mcdon

Reputation: 305

Select rows which appear more than given number of times in table

I have table in mysql db from which I need to select thoose rows, which are duplicated more than given number of times, but result should not be merged (if that row was five times in table, I want it to appear five times in result)

Table has three columns: Id, Name, Surname

I have tried grouping like this:

SELECT t.Name, t.Surname FROM table t
WHERE t.Id IN (
    SELECT tt.Id FROM table tt
    GROUP BY tt.Name, tt.Surname
    HAVING count(*) > 10
)

I know where is my mistake - in this subquery, because as I understand it returns only one id per unique Name + Surname, but I don't know how to achieve desired behavior properly.

Upvotes: 1

Views: 425

Answers (1)

Mureinik
Mureinik

Reputation: 311308

One way to go about this would be to join your original table with the aggregate query:

SELECT t.*
FROM   t
JOIN   (SELECT   name, surname, COUNT(*)
        FROM     t
        GROUP BY name, surname
        HAVING   COUNT(*) > 10) aggr ON t.name = aggr.name AND 
                                        t.surname = aggr.surname

Upvotes: 3

Related Questions