SQL query for finding records where count > 1

833    Asked by rachit_6798 in SQL Server , Asked on Mar 21, 2021

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.

Answered by Rachel Kerr

Your Answer

Interviews

Parent Categories