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.

score 6 · Accepted Answer · edited Apr 11 '19 at 06:27

6

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;

edited Apr 11 '19 at 06:27

abdul qayyum

535
1
17
39

answered Sep 11 '13 at 11:36

Gordon Linoff

1,242,037
58
646
786

score 3 · Answer 2 · edited Mar 08 '21 at 22:46

3

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;

edited Mar 08 '21 at 22:46

Brien Foss

3,336
3
21
31

answered Sep 11 '13 at 07:58

Dba

6,511
1
24
33

it seems having count(*) filter both userid and videoid. So I got users who watched more than two videos, and also watch a video for more than two times. – 蒋艾伦 Sep 11 '13 at 08:04
If you need to get the count based on userid and videoid, please find the modified answer. – Dba Sep 11 '13 at 08:36
Double checked, the second SQL will select userid, and videos which has been watched twice by a same user. I want to get users that have watched more than two videos, and the videolist he watched. – 蒋艾伦 Sep 11 '13 at 08:46

score 2 · Answer 3 · edited Nov 05 '21 at 16:38

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

A user could watch a video twice. It'd better not use a self-join, because this table has almost 1 million records, I bet. — 蒋艾伦, Sep 11 '13 at 09:02

score 0 · Answer 4 · answered Sep 11 '13 at 09:34

0

The below will give users who have watched more than two different videos.

 SELECT userid, count(distinct video_id)
 FROM   table_play_list
 WHERE SOME CONDICTION
 GROUP BY user_id
 having count(distinct video_id) >2;

answered Sep 11 '13 at 09:34

DB_learner

1,026
9
15

ethemsulan · Answer 5 · 2014-12-09T08:31:32.070

If you use Oracle PL/SQL you can use like this:

SELECT column1, column2  
FROM 
    (
    SELECT column1, column2, COUNT(column1)
    OVER (PARTITION BY column1) AS cnt 
    FROM test 
    GROUP BY column1, column2
    ORDER BY column1 
    ) 
WHERE cnt > 2

If you use standard SQL you can use like this:

SELECT column1, column2 
FROM test 
WHERE column1 IN
    (
        SELECT column1 
        FROM
         (
             SELECT column1, column2 
             FROM test 
             GROUP BY column1, column2 
             ORDER BY column1
         )  
         GROUP BY column1 
         HAVING COUNT(column1) > 2
     ) 
GROUP BY column1, column2 
ORDER BY column1

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

5 Answers5