CODEWITHSUNDEEP
sql
Mukesh Marimuthu
Mukesh Marimuthu

Reputation: 145

Need to find the count of user who belongs to different depts

I have table with dept,user and so on, I need to find the number of count of user that belongs to different combinations of the dept.

Lets consider I've a table like this:

dept   user  
1      33  
1      33  
1      45  
2      11  
2      12   
3      33  
3      15

Then I've to find the uniq user and dept combination: something like this: select distinct dept,user from x;

Which will give me result like :

Dept    user  
1         33  
1         45  
2         11  
2         12  
3         33  
3         15

which actually removes the duplicates of the combination: And here's the thing which i need to do :

My output should look like this:

dep_1_1 dep_1_2 dep_1_3 dep_2_2 dep_2_1 dep_2_3 Dep_3_1 Dep_3_2 Dep_3_3  
2          0     1       2       0         0      1       0      2

So, Basically I need to find the count of common users between all the combinations of departments

Thanks for the help

Upvotes: 0

Views: 50

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can get a row for each department combination using a self-join of your Distinct Select:

with cte as
 (
   select distinct dept,user from x 
 )
select t1.dept, t2.dept, count(*)
from cte a st1 join cte as t2
  on t1.user = t2.user -- same user
 and t1.dept < t2.dept -- different department
group by t1.dept, t2.dept
order by t1.dept, t2.dept

Upvotes: 1

Related Questions