How to find duplicates in two columns and count for each row found

[cash1981]

Hello all.

I have a little advanced task at hand. I want to search in a table, and find duplicates and count each duplicate found.

Imagine this table

Table | Column Name

Userinfo
Firstname | Lastname

In this table there are many with the same firstname and lastname.
I would like an sql statement which find these duplicates and for each duplicate also writes how many occurences it found
So the output I would like is something like this:

John Smith 5
Eva Something 8
and so on.

Does anybody have any tips?

[cash1981]

I have come this far. I think this code finds duplicate, but I am missing the counting of them


select firstname, lastname
from nhst.userinfo
where firstname in
(select firstname from nhst.userinfo
group by firstname
having count(firstname)>1) and
lastname in
(select lastname from nhst.userinfo
group by lastname
having count(lastname)>1)
order by firstname

[guelphdad]

your query will return both John Smith and John and Mary Wales from the sample data below.

So if you have
John Wales
Mary Wales
John Cosmo
John Smith
John Grabby
John Smith
John Smith

Is that what you wanted or only John Smith since his first and last names are duplicates?

your query selects John Wales because the count of all Johns is more than one. It selects Mary Wales because the count of Wales is more than one.

Your first post does not seem to suggest that is what you want, but only when first and last name together is duplicated.

If you want duplicates of firstname and lastname, (i.e. only pull John Smith from the example above) you want:


SELECT
firstname,
lastname,
count(*) as total
FROM yourtable
GROUP BY
firstname,
lastname
HAVING
count(concat(firstname,lastname)) > 1

[cash1981]

Thanks a bundle. That was exactly what I was after