Join a sub query with group by statement

SELECT * 
FROM `member` a 
join (SELECT password, count(password) as pw_count 
FROM `member`
group by password
HAVING COUNT(password) > 1) b on b.password = a.password

Find members that have the same password

More Advanced – Get minimum member ID from members that have same password:

SELECT min(a.id) as m_id, b.pw_count
FROM `member` a 
join (SELECT password, count(password) as pw_count 
FROM `member`
group by password
HAVING COUNT(password) > 1) b on b.password = a.password
group by b.pw_count

 

Reference: