Cacher is the code snippet organizer for pro developers

-- LIST USING GROUP BY
SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

![](https://cdn.cacher.io/attachments/u/36l276oo60sz8/UQE6w1Gm3ixfcAcKj7SSLLUJg1nmo1UJ/69gomrtw1.png)

-- LIST SEPARATELY USING GROUP BY:
SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email

![](https://cdn.cacher.io/attachments/u/36l276oo60sz8/D0j47jKoYDxW7SBFOCbDuQb0Qlxey83o/Screenshot_2021-04-28_095612.png)

-- LIST USING SELF-JOIN
SELECT P.* FROM POLICY P
INNER JOIN
 (
    SELECT P.POLICY_NO, P.ENDORSEMENT_NO, P.PROPOSAL_STATUS
    FROM POLICY P
    GROUP BY P.POLICY_NO, P.ENDORSEMENT_NO, P.PROPOSAL_STATUS
    HAVING P.PROPOSAL_STATUS = 1 and count(*) > 1
) MULTI_GRP ON 
    MULTI_GRP.POLICY_NO = P.POLICY_NO AND
    MULTI_GRP.ENDORSEMENT_NO = P.ENDORSEMENT_NO
  WHERE
    P.PROPOSAL_STATUS = 1;