MySQL Select Distinct Count

select user_id, count(distinct video_id) from TABLE group by user_id;

For total count...

select distinct count(video_id) from Table
where...
group...

For now a total count of unique user_id, video_id pairs can be calculated with the following query

select count(distinct user_id, video_id) from table;

If you want to get the total count for each user, then you will use:

select user_id, count(distinct video_id)
from data
group by user_id;

Then if you want to get the total video_ids then you will wrap this inside of a subquery:

select sum(cnt) TotalVideos
from
(
  select user_id, count(distinct video_id) cnt
  from data
  group by user_id
) d

See SQL Fiddle with Demo of both.

The first query will give you the result of 2 for each user_id and then to get the total of all distinct video_ids you sum the count.

Related