CODEWITHSUNDEEP
mysqlsql
Mj1992
Mj1992

Reputation: 3504

Mysql query for distinct count based on a specific column

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.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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;

Upvotes: 1

Related Questions