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