mankee mankee - 23 days ago 5
SQL Question

SELECT records on distinct dates SQL

I want to obtain all users who have 3 or more records in the DB however they cannot have the same date. Example table:

User1 40 12/11/15
User1 33 13/11/16
User1 23 04/09/16
User2 21 30/09/16
User3 12 12/11/16
User3 54 12/11/16
User3 99 04/09/16


So from here, I want to obtain User1 only, User3 has 3 records, but since 2 of them are on the same day, he doesn't qualify. (it is okay if two or more records are on the same day, as long as there are at least 3 on distinct dates, so if we add one more record for user3 on a different date, he would also satisfy the requirements.)

This is my query so far:

SELECT id, price, date
FROM Table
WHERE id
IN (

SELECT id
FROM Table
GROUP BY id
HAVING COUNT( ocust ) >=3
)


It takes care of the requirement to have 3 or more records, however I don't know how to take care of the distinct date requirement.

Answer

You just need to add DISTINCT to the COUNT, and change ocust (whatever that is) to date:

SELECT id, price, date
FROM Table
WHERE id
IN (
    SELECT id
    FROM Table
    GROUP BY id
    HAVING COUNT(DISTINCT date ) >=3   -- Added DISTINCT, changed 'ocust' to 'date'
)
Comments