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.
4 Answers
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(*) count FROM PAYMENT GROUP BY account, user_id , date HAVING COUNT(*) > 1 Update If 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 , date HAVING COUNT(*) > 1 Try this query:
SELECT column_name FROM table_name GROUP BY column_name HAVING 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 ); create table payment( user_id int(11), account int(11) not null, zip int(11) not null, dt date not null ); insert into payment values (1,123,55555,'2009-12-12'), (1,123,66666,'2009-12-12'), (1,123,77777,'2009-12-13'), (2,456,77777,'2009-12-14'), (2,456,77777,'2009-12-14'), (2,789,77777,'2009-12-14'), (2,789,77777,'2009-12-14'); select foo.user_id, foo.cnt from (select user_id,count(account) as cnt, dt from payment group by account, dt) foo where foo.cnt > 1; 
