CODEWITHSUNDEEP
mysqlsqlcount
gbvisconti
gbvisconti

Reputation: 412

Counting skill for each person

I'm trying to create a Venn diagram. I already have the javascript using d3js. What I need now is the following.

There are 3 tables

Person (id_person, name_person)
Skill (id_skill, name_skill)
Person_Skill(id_person, id_skill)

How can I count the number of people for each subset of id_skill (with sql)?

I have write this php script to create all sets of id_skill

 function powerSet($in, $minLength = 1) { 
   $count = count($in); 
   $members = pow(2,$count); 
   $return = array(); 
   for ($i = 0; $i < $members; $i++) { 
      $b = sprintf("%0".$count."b",$i); 
      //$out = array(); 
      $member = '';
      for ($j = 0; $j < $count; $j++) { 
         if ($b{$j} == '1') $member .= $in[$j]. ","; 
      } 
      if($member != '') $out = $member;
      if (count($out) >= $minLength) { 
         $return[] = $out; 
      } 
   } 
   return $return; 
}

Example

Skill

id_skill       name_skill
1              PHP
2              SQL


Person

id_person         name_person
1                 'Name1'
2                 'Name2

Person_Skill

id_person              id_skill
1                      1
1                      2
2                      1

For the set of id_skill {1} => count = 1 (because only person 2 knows just this)
For the set of id_skill {2} => count = 0 (because person 1 also knows skill 1)
For the set of id_skill {1, 2} => count = 1 (person 1 knows both)

I'ts like use IN CLAUSE matching all IDs. Database is MYSQL

Upvotes: 1

Views: 204

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You can generate the set of ids for each person using aggregate string concatenation. You can then count them.

Each database has a different way of doing aggregate string concatenation. The following shows the MySQL query for this:

select skills, count(*) as cnt
from (select sk.id_person, group_concat(distinct sk.id_skill order by sk.id_skill) as skills
      from person_skill sk
      group by sk.id_person
     ) ps
group by skills;

Upvotes: 2

Related Questions