Count distinct values over multiple columns

Question

Similar question to this however I want to count how many different distinct values appear for one user_id. If I had the following table:

USER_ID  DATA
123      abc
234      abc
123      def
456      def
123      abc

I would want the following results:

USER_ID  COUNT
123      2
234      1
456      1

Because '123' has both 'abc' and 'def' data values, where as '234' and '456' only have the one distinct data value.

Currently I have the following pseudo-code:

rows = SELECT DISTINCT user_id FROM table
for for in rows:
     result = SELECT DISTINCT user_id, data FROM table WHERE user_id = <row>
     if result.rowcount > 2:
          doSomething()

But this takes a long time as it has to make a call for each user_id.

Can this be done in one MySQL command?

Mike Brant · Accepted Answer · 2015-04-20 19:52:58Z

3

Just use COUNT() aggregation along with GROUP BY

SELECT
    user_id,
    COUNT(DISTINCT data) AS distinct_data_count
FROM table
GROUP BY user_id

answered Apr 20, 2015 at 19:52

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

Comments

betseyb · Accepted Answer · 2015-04-20 19:52:47Z

0

I'm a little rusty, but can't you do

SELECT DISTINCT user_id, COUNT(DISTINCT data) as dc from table;

answered Apr 20, 2015 at 19:52

betseyb

1,3422 gold badges18 silver badges38 bronze badges

Comments

starko · Accepted Answer · 2015-04-20 19:56:22Z

0

This is SQL Fiddle

select user_id, count(distinct dat) from test
group by user_id

answered Apr 20, 2015 at 19:56

starko

1,14911 silver badges26 bronze badges

Collectives™ on Stack Overflow

Count distinct values over multiple columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related