Reputation: 4901
+----------+----------+
| 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.
Upvotes: 45
Views: 95654
Reputation: 614
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;
Upvotes: 2
Reputation: 109
For total count...
select distinct count(video_id) from Table
where...
group...
Upvotes: 1
Reputation: 247680
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.
Upvotes: 94
Reputation: 4930
select user_id, count(distinct video_id) from TABLE group by user_id;
Upvotes: 4