SQL query to search for records where count> 1

Question

I have a table called PAYMENT . Inside this table, I have a user ID, account number, zip code and date. I would like to find all records for all users who have more than one payment per day with the same account number.

UPDATE: In addition, there should be a filter, not just records whose zip code is different.

This is what 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 something like this:

  |  user_id |  count |
 |  1 |  2 |

How would you express this in a SQL query? I thought about myself, but for some reason my account is wrong.

Answer 1

Use the HAVING clause and GROUP Fields that make a unique string

Below you will find

all users who 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 include only those that have a separate ZIP, you can first get a great set, and then run the HAVING / GROUP BY command

  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 

Answer 2

Try this query:

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

Answer 3

 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; 

Answer 4

I would not recommend the HAVING keyword for beginners, this is mostly for legacy purposes .

I don’t understand what is the key for this table (is it completely normalized , I wonder?), Therefore, it is difficult for me to follow your requirements:

I would like to find all records for all users who have more than one payment per day with the same account number ... In addition, there should be a filter than just counting records whose zip code is different.

So, I took a literal interpretation.

Below is a more detailed one, but it may be easier to understand and therefore support (I used CTE for the PAYMENT_TALLIES table, but it could be 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 ); 

Answer 5

  delete t1 from @tbl t1 where 1<(select count(ID) from @tbl where ID=t1.ID)