Group by two fields, and having count() on first field

Question

I have a table that stored users play list, a video can be viewed by multiple users for multiple times. A records goes like this:

videoid, userid, time
123,     abc   , 2013-09-11

It means user(abc) has watched video(123) on 2013-09-11

Now I want to find distinct users watched video list (no duplication), and only show the users that have watched more than two videos.

SELECT videoid, userid 
FROM table_play_list
WHERE SOME CONDICTION
GROUP BY userid, videoid

The sql only select distinct users watchlist, I also want to filter users that have watched more than two different videos.

I know I have to google and read the documentation first, some said 'HAVING' could solve this, unfortunately, I could not make it.

Answer 1

If I understand correctly, you are looking for users who watched more than two different videos. You can do this by using count(distinct) with a partition by clause:

select userid, videoid
from (SELECT userid, videoid, count(distinct videoid) over (partition by userid) as cnt
      FROM table_play_list
      WHERE <ANY CONDITION>
     ) t
where cnt > 2;

Answer 2

Try like this,

SELECT userid, count(*)
FROM   table_play_list
--WHERE SOME CONDITION
GROUP BY user_id
having count(*) >2;

Try this if you need to get the count based on userid and videoid(users who watch the same video more than two times).

SELECT userid, videoid, count(*)
FROM   table_play_list
--WHERE SOME CONDITION
GROUP BY user_id, video_id
having count(*) >2;