SQL query for finding records where count > 1

Question

I have a table named PAYMENT. Within this table, I have a user ID, an account number, a ZIP code, and a date. I would like to find all records for all users that have more than one payment per day with the same account number.

UPDATE: Additionally, there should be a filter than only counts the records whose ZIP code is different.

This is how the table looks like:

| user_id | account_no | zip   |      date |

|       1 |        123 | 55555 | 12-DEC-09 | 

|       1 |        123 | 66666 | 12-DEC-09 |

|       1 |        123 | 55555 | 13-DEC-09 |

|       2 |        456 | 77777 | 14-DEC-09 |

|       2 |        456 | 77777 | 14-DEC-09 |

|       2 |        789 | 77777 | 14-DEC-09 |

|       2 |        789 | 77777 | 14-DEC-09 |

The result should look similar to this:

| user_id | count |

|       1 |     2 |

How would you express this in a SQL query? I was thinking self-join but for some reason my count is wrong.

Answer 1

To make the row unique, try using the HAVING clause and GROUP By. 

Refer to this video to learn HAVING clause in detail:

The following code will find all the users that have more than one payment per day with the same account number:

SELECT user_id ,COUNT(*) count

FROM PAYMENT

GROUP BY account,user_id ,date

Having COUNT(*) > 1

Note: If you want to include only those users who have a distinct ZIP, then you can get the distinct set first. Now, you can perform HAVING/GROUP BY like this:

Refer to this video if you want to learn GROUP BY clause in detail:

 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 , date

HAVING COUNT(*) > 1

To master SQL statements, queries and become proficient in SQL queries, enroll in an industry-recognized SQL Online Course. 

Answer 2

In the case where you would like to identify an individual who has made payments multiple times within a day using the same account and different zip codes, self join comes into play. It enables you to evaluate the records with the same user_id , the same account_no and the same date but with different zip codes.

To implement this you can use below query:

SELECT 

    p1.user_id, 

    COUNT(*) AS count

FROM 

    PAYMENT p1

JOIN 

    PAYMENT p2 

ON 

    p1.user_id = p2.user_id 

    AND p1.account_no = p2.account_no

    AND p1.date = p2.date

    AND p1.zip <> p2.zip  -- Ensure ZIP codes are different

GROUP BY 

    p1.user_id

HAVING 

    COUNT(*) > 1;