SQL query for finding records where count > 1

by

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

More Related Contents:

  1. How to use count and group by at the same select statement
  2. SQL to Entity Framework Count Group-By
  3. Mysql: how to select groups having certain values?
  4. PostgreSQL Where count condition
  5. Conditional SQL count
  6. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  7. SQL – HAVING vs. WHERE
  8. Is there any difference between GROUP BY and DISTINCT
  9. must appear in the GROUP BY clause or be used in an aggregate function
  10. GROUP BY to combine/concat a column [duplicate]
  11. Select count(*) from multiple tables
  12. JOIN two SELECT statement results
  13. Ms Access Query: Concatenating Rows through a query
  14. Conditional Count on a field
  15. Group by month and year in MySQL
  16. SQL Group by & Max
  17. How to count instances of character in SQL Column
  18. Fastest way to determine if record exists
  19. SQL count(*) performance
  20. Count Returning blank instead of 0
  21. Huge performance difference when using GROUP BY vs DISTINCT
  22. sql group by only rows which are in sequence
  23. GROUP BY without aggregate function
  24. MySQL GROUP BY behavior
  25. SQL: How do you select only groups that do not contain a certain value?
  26. Get values from first and last row per group
  27. How to return a incremental group number per group in SQL
  28. SQL query to group by day
  29. COUNT(id) vs. COUNT(*) in MySQL
  30. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?

Leave a Comment