Mysql query for distinct count based on a specific column

Question

I've situation in which I want to find out the occurrence of items in a particular column without grouping the elements. Here's the situation.

User Table :

UserID     :     Name
  1              User1
  2              User2

Tags :

TagID      :     Tag 
  1              Tag1  
  2              Tag2  
  3              Tag3

UserTags :

 UserID     :    TagID
    1             1
    1             2
    2             3

Now the following query returns.

select ut.tagid
     ,t.tag,ut.userid
from tags t
inner join usertags ut on ut.tagID=t.tagid
order by userid

Result:

 TagID    :   Tag     :  UserID
   1          Tag1         1
   2          Tag2         1
   3          Tag3         2

Which is correct. I just want to add one more column to this result set such that it tells me the distinct count of UserID in the result set. So that my result set looks like this.

Required Result Set :

 TagID    :   Tag     :  UserID  :  UserIDoccurrence
   1          Tag1         1             2
   2          Tag2         1             2
   3          Tag3         2             1

As you can see. Since user with userID = 1 has two results. I got 2 in the UserIDOccurrence column and 1 for UserID 2.

Gordon Linoff · Accepted Answer · 2014-09-04 14:57:23Z

1

There are several approaches. This uses a correlated subquery:

select ut.tagid, t.tag, ut.userid,
       (select count(*) from usertags ut2 where ut2.userid = ut.userid) as totalcount
from tags t inner join
     usertags ut
     on ut.tagID = t.tagid
order by userid;

edited Sep 4, 2014 at 14:57

answered Sep 4, 2014 at 14:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Mysql query for distinct count based on a specific column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related