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.

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

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

Solution 3

This is probably best handled with analytics (window functions). Without analytics you will probably need a self-join.

SQL> WITH table_play_list AS (
  2     SELECT 123 videoid, 'a' userid FROM dual UNION ALL
  3     SELECT 125 videoid, 'a' userid FROM dual UNION ALL
  4     SELECT 123 videoid, 'b' userid FROM dual UNION ALL
  5     SELECT 123 videoid, 'b' userid FROM dual UNION ALL
  6     SELECT 123 videoid, 'c' userid FROM dual
  7  )
  8  SELECT videoid, userid,
  9         COUNT(*) over(PARTITION BY userid) nb_video
 10    FROM table_play_list;
 
   VIDEOID USERID   NB_VIDEO
---------- ------ ----------
       123 a               2
       125 a               2
       123 b               2
       123 b               2
       123 c               1

This lists all user/video and the total number of videos watched by each user. As you can see user b has watched the same video twice, I don't know if it's possible in your system.

You can filter with a subquery:

SQL> WITH table_play_list AS (
  2     SELECT 123 videoid, 'a' userid FROM dual UNION ALL
  3     SELECT 125 videoid, 'a' userid FROM dual UNION ALL
  4     SELECT 123 videoid, 'b' userid FROM dual UNION ALL
  5     SELECT 123 videoid, 'b' userid FROM dual UNION ALL
  6     SELECT 123 videoid, 'c' userid FROM dual
  7  )
  8  SELECT *
  9    FROM (SELECT videoid, userid,
 10                 COUNT(*) over(PARTITION BY userid) nb_video
 11            FROM table_play_list)
 12   WHERE nb_video > 1;
 
   VIDEOID USERID   NB_VIDEO
---------- ------ ----------
       123 a               2
       125 a               2
       123 b               2
       123 b               2