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

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.

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;

Try this query:

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

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 > 1
              );