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.