0

I have a table called "people".

I want to check if there are duplicates.

ID  | NAME | AGE | 
----|------|-----|
1   | Max  | 21  |
2   | Tom  | 30  |
3   | Jim  | 45  |
4   | Jim  | 45  |
5   | Max  | 21  |
6   | Max  | 21  |
7   | Tom  | 30  |
8   | Bob  | 60  |
9   | Jim  | 45  |
10  | Nik  | 50  |

Nik and Bob have only one occurrence. The others (Max, Tom and Jim) have more than one occurrence. I want to get this result:

NAME | AGE | COUNT
Max  | 21  | 3
Tom  | 30  | 2
Jim  | 45  | 3

I've tried the following code

select 
    name, count(name),
    age, count(age)
   
FROM
    people
GROUP BY 
    name,
    age
HAVING 
       (COUNT(name) > 1) AND 
       (COUNT(age) > 1) ;

Can someone help me? Thank you.

Massimo
  • 1
  • 1

1 Answers1

0

The ages are always duplicated with for the duplicate names, so there's no need to count ages. Just count the duplicate names.

SELECT name, age, COUNT(*) AS count
FROM people
GROUP BY name, age
HAVING count > 1
Barmar
  • 741,623
  • 53
  • 500
  • 612