Jako
Jako

Reputation: 4901

MySQL Select Distinct Count

+----------+----------+
| 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

Answers (4)

bazeusz
bazeusz

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

user3242558
user3242558

Reputation: 109

For total count...

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

Upvotes: 1

Taryn
Taryn

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

Trenton Trama
Trenton Trama

Reputation: 4930

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

Upvotes: 4

Related Questions