SQL query for finding records where count > 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT  user_id , COUNT(*) countFROM  PAYMENTGROUP BY account, user_id , dateHAVINGCOUNT(*) > 1

UpdateIf you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT     user_id,    account_no ,     date,        COUNT(*) FROM    (SELECT DISTINCT            user_id,            account_no ,             zip,             date         FROM            payment             )         payment GROUP BY        user_id,    account_no ,         dateHAVING COUNT(*) > 1

Try this query:

SELECT column_name  FROM table_name GROUP BY column_nameHAVING COUNT(column_name) = 1;

I wouldn't recommend the HAVING keyword for newbies, it is essentially for legacy purposes.

I am not clear on what is the key for this table (is it fully normalized, I wonder?), consequently I find it difficult to follow your specification:

I would like to find all records for all users that have more than one payment per day with the same account number... Additionally, there should be a filter than only counts the records whose ZIP code is different.

So I've taken a literal interpretation.

The following is more verbose but could be easier to understand and therefore maintain (I've used a CTE for the table PAYMENT_TALLIES but it could be a VIEW:

WITH PAYMENT_TALLIES (user_id, zip, tally)     AS     (      SELECT user_id, zip, COUNT(*) AS tally        FROM PAYMENT       GROUP           BY user_id, zip     )SELECT DISTINCT *  FROM PAYMENT AS P WHERE EXISTS (               SELECT *                  FROM PAYMENT_TALLIES AS PT                WHERE P.user_id = PT.user_id                      AND PT.tally > 1              );