MySQL Select Distinct Count
mysql
Solution
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.
Problem
``` +----------+----------+ | user_id | video_id | +----------+----------+ | 1 | 1 | | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | +----------+----------+ ``` I have a table setup similar to the one above. I would like to return a total count from my query. For each `user_id` they need to have a `DISTINCT video_id`. So above, `user_id = 1` would have 2 unique video_id's and `user_id = 2` would have 2 unique video_id's. This would make the total 4. I'm not sure the best way to organize my query to achieve the desired result. Basically for each user_id, I need something like, `COUNT(DISTINCT video_id)` I would like the final result just to return total count of everything.