SQL query for finding records where count 1

Question

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

User · Accepted Answer

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

User · Answer

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  gt  1

User · Answer

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  gt  1

User · Answer

Try this query   SELECT column name   FROM table name  GROUP BY column name HAVING COUNT column name    1

[sql] SQL query for finding records where count > 1

Examples related to sql

Examples related to count

Examples related to group-by

Examples related to having