Geek Answers Handbook

SQL selects a query to group 2 columns

I am trying to understand this problem - it seems pretty direct

The table below shows which user has watched the video,

  topic_user_id |  topic_id |  user_id
      1 |  10 |  3
      2 |  10 |  4
      3 |  11 |  5
      4 |  11 |  3
      5 |  12 |  6
      6 |  13 |  6
      7 |  14 |  7
      8 |  11 |  8
      9 |  11 |  9
      10 |  14 |  10
      11 |  15 |  eleven
      12 |  16 |  eleven
      13 |  17 |  eleven

Now, to find out how many users have watched a particular video, I have the following query.

HOW MANY USERS SEE A SPECIFIC VIDEO

  select count (distinct (user_id)) as 'Number of Users', topic_id from topic_user
 where user_id is not null
 group by topic_id

Exit

  Number of Users |  topic_id 
     2 |  10     
     4 |  eleven     
     1 |  12     
     1 |  thirteen     
     2 |  14     
     1 |  fifteen     
     1 |  16     
     1 |  17     

 Read as: 2 users watched topic 10, 4 watched topic 11 and so on

It works great. But I am looking to find:

How many users watched 1 video
How many users watched 2 videos
How many users watched 3 videos

The result should be something like

  Number of Users |  Number of videos watched
       6 |  1
       2 |  2
       1 |  3

 Read as - 6 people watched only 1 video, 2 people watched 2 videos and so on.

You need help.

Thank you in advance

+4
source share
5 answers

It might be simpler, but the subquery will work

select videos as 'Number of videos', count(user_id) as 'Num of Users' from ( select count(distinct(topic_id)) as videos, user_id from topic_user group by user_id ) sub group by videos
+2
source 
 select un as `Number of Video watched`, count(t.user_id) from (select user_id, count(*) as `cnt` from topic_user group by user_id) t INNER JOIN( SELECT 1 as `n` FROM DUAL UNION SELECT 2 as `n` FROM DUAL UNION SELECT 3 as `n` FROM DUAL) u ON un = t.cnt group by un

Fiddle: http://sqlfiddle.com/#!2/844ee/15

+1
source

Try the following:

 Select first_table.count(distinct(user_id)) as 'Number of Users', SUM(Count(topic_id)) from (select count(distinct(user_id)) as 'Number of Users',topic_id from topic_user where user_id is not null group by topic_id) as first_table group by Count(topic_id)
0
source

try it

 SELECT videos AS `Number of videos`,COUNT(user_id) AS `Num of Users` FROM (SELECT COUNT(DISTINCT(topic_id)) AS videos,user_id FROM topic_user GROUP BY user_id) sub_query GROUP BY videos
0
source

"How many users have watched n videos" can be interpreted in two ways:

1) How many users watched n videos, regardless of the name? The same title can be viewed twice.

 select videoCount, count(*) as userCount from ( select count(*) as videoCount from topic_user group by user_id ) t group by videoCount

2) How many users watched n different videos.

 select videoCount, count(*) as userCount from ( select count(distinct topic_id) as videoCount from topic_user group by user_id ) t group by videoCount
0
source

More articles:


All Articles