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.
