MySQL Select Distinct Count

Question

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

People interested in counting distinct rows on several columns (e.g. with wildcard *) should check this answer. — Skippy le Grand Gourou
– Skippy le Grand Gourou, Commented Sep 2, 2019 at 10:50

Taryn · Accepted Answer · 2013-03-29 20:31:47Z

95

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.

answered Mar 29, 2013 at 20:31

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Irfan Ahmed Over a year ago

@bluefeet On a side note, what if we have another column say audio_id and we want to get count of it too in the same query?

Irfan Ahmed Over a year ago

In reference to your this answer stackoverflow.com/questions/12789396/… but I need to select it as distinct like above.

Taryn Over a year ago

@IrfanAhmed Off hand I'm not sure. I'd say post a new question with your requirements so it could be answered.

Trenton Trama · Accepted Answer · 2013-03-29 20:29:40Z

4

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

answered Mar 29, 2013 at 20:29

Trenton Trama

4,9301 gold badge25 silver badges27 bronze badges

2 Comments

Jako Over a year ago

Is there a way to return just a total count rather than each users count?

Trenton Trama Over a year ago

Yes, and I believe bluefeet answered that very well for you.

bazeusz · Accepted Answer · 2019-07-23 09:09:36Z

2

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;

answered Jul 23, 2019 at 9:09

bazeusz

6646 silver badges11 bronze badges

Comments

aksu · Accepted Answer · 2014-12-16 14:59:33Z

1

For total count...

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

edited Dec 16, 2014 at 14:59

aksu

5,2365 gold badges26 silver badges39 bronze badges

answered Feb 18, 2014 at 0:02

user3242558

1091 silver badge11 bronze badges

2 Comments

bazeusz Over a year ago

I know it's old but just for correctness the above will not return total count of unique values as asked in the original question.

TV-C-1-5 Over a year ago

This will not return distinct - but will return count - this is an erroneous answer

Collectives™ on Stack Overflow

MySQL Select Distinct Count

4 Answers 4

3 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related