CODEWITHSUNDEEP
mysql
Hasan Al-Natour
Hasan Al-Natour

Reputation: 2066

mysql query with two level grouping

i have the following table structure :

Video_status : to capture all views on videos that i have 

    +------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| video_id   | int(10) unsigned | NO   | MUL | NULL                |                |
| status     | varchar(255)     | NO   |     | NULL                |                |
| user_id    | int(10) unsigned | NO   | MUL | NULL                |                |
| course_id  | int(10) unsigned | NO   | MUL | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+------------+------------------+------+-----+---------------------+----------------+

what am trying to get is the number of user views per day where the user_id is unique for every day grouped by date , so at the end i can get everyday and how many unique users viewed the videos in that day. any suggestions ?

Regards

Upvotes: 0

Views: 25

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Grouping by date and video id this will get you count of unique users that in a particular day has viewed that video.

select 
  date_format(t.the_date, '%m-%d-%Y') as date,
  video_id,
  count(distincct user_id) as count_unique_users
from
  video_status
group by 
  date_format(created_at, '%m-%d-%Y'),
  video_id

If you only need unique users who viewed all videos then remove the video_id part from select and group by clause.

Upvotes: 2

Related Questions