CODEWITHSUNDEEP
mysqldatabasegroup-by
Ohhhnick
Ohhhnick

Reputation: 5

MySQL query that selects all users that have more than one entry per day

I want to get all the USER_ID for users who have posted more than one thing per day,

I tried originally tried this

    SELECT USER_ID, count(DISTINCT cast(POSTING_DATE as DATE))
    AS NUM_DAYS_OF_DUPLICATES FROM POSTING_TABLE
    WHERE USER_ID IN
        (SELECT USER_ID FROM POSTING_TABLE
        GROUP BY CAST(POSTING_DATE AS DATE) HAVING count(*) >= 2)
    GROUP BY USER_ID ORDER BY NUM_DAYS_OF_DUPLICATES DESC;

Then this works for a specific USER_ID

    SELECT USER_ID FROM POSTING_TABLE WHERE USER_ID = 30 
    GROUP BY cast(POSTING_DATE AS DATE) 
    HAVING count(cast(POSTING_DATE AS DATE)) > 1

The above gives me the correct result, however when I run the query on the entire table without specifying a USER_ID it does not.

eg.,

table structure USER_ID, POSTING_DATE ...

    USER_ID POSTING_DATE
    1       10-10-13
    1       10-10-13
    1       10-12-13
    1       10-12-13
    2       10-10-13
    2       10-10-13
    3       10-10-13
    4       10-12-13

Where the result would give me

    USER_ID  NUM_DAYS_WITH_MORE_THAN_ONE_POSTING
    1        2
    2        1
    3        0
    4        0

Also if we can omit the 0's

Upvotes: 0

Views: 836

Answers (1)

Rik
Rik

Reputation: 2032

This is the solution

select x.user_id, count(x.num_days)
from
(
select USER_ID, COUNT(USER_ID) AS NUM_DAYS
from data1 
group by user_id, posting_date
having count(user_id) > 1
) x
group by 1

Working SQL Fiddle

(I used a varchar for date for simplicity but it should work fine with date too. You can check with your own database)

Upvotes: 1

Related Questions