Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

SQL query for finding records where count > 1

Asked by Rachit Gupta Mar 21, 2021 1.7K views 1 answer
Share

About this question

I have a table named PAYMENT. In 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 it be expressed in a SQL query? I was thinking about self-joining but for some reason my count was wrong.

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.