Counting rows without duplicates MYSQL

Question

I have a table such as the following

id_profile, id_user
1         , 5
1         , 4
1         , 4
1         , 4
2         , 1
2         , 5
2         , 2

I'm trying to count id_user but I have to eliminate duplicates. So, I want a result like

id_profile, count
1         , 2
2         , 3

Any suggestions?

jarlh · Accepted Answer · 2016-08-11 14:07:35Z

3

Do a GROUP BY, use count distinct:

select id_profile, count(distinct id_user) as count
from tablename
group by id_profile

answered Aug 11, 2016 at 14:07

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

1 Comment

THANK YOU VERY VERY MUCH

Kurt Van den Branden · Accepted Answer · 2016-08-11 16:20:35Z

1

You can use a sub-query to filter distinct values and then use count, group by on that if that's easier to understand.

SELECT id_profile, COUNT(id_user)
    FROM(SELECT DISTINCT id_profile,id_user FROM tablename)
    GROUP BY id_profile

13.2k10 gold badges83 silver badges92 bronze badges

answered Aug 11, 2016 at 15:28

113 bronze badges