MySql Split Query Group Percentage
<p>I am trying to do the following but I cannot manage to get it right yet :(. </p>

<p>I have these tables:</p>

<pre><code>table1 -&gt; tb1_id, tb1_name 

Sample Data:
--------------
1  group1
2  group2
3  group3
4  group4
5  group5

table2 -&gt; tb2_id, tb2_sector, tb2_tb3_id   

Sample Data:
--------------
1  alpha   1
2  beta    2
3  gamma   2
4  delta   2
5  epsilon 4

table3 -&gt; tb3_id, tb3_mid, tb3_section 

Sample Data:
--------------
1   234   alpha,beta,gama,delta
</code></pre>

<p>This is the output that I am looking for:</p>

<pre><code>Name    Count     %
------  -----    -----
group1   1       25%
group2   3       75%
group3   0       0%
group4   0       0%
group5   0       0%
</code></pre>

<p>Basically I need a split a column value delimited by a comma (tb3_section in table3) and then find the right group for each value (table2 gives me the group id to link with table1) and then do a total count by group and get the percentage (assuming total is 100%).</p>

<p>This is the query I tried so far:</p>

<p>I searched for split value samples and found one that does the split by creating a numbers table first: </p>

<pre><code>create table numbers (
  `n` INT(11) SIGNED 
  , PRIMARY KEY(`n`)
) 

INSERT INTO numbers(n) SELECT @row := @row + 1 FROM 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, 
(SELECT 0 UNION ALL SELECT 1) t8, 
(SELECT @row:=0) ti;
</code></pre>

<p>Afterwards, I did this:</p>

<pre><code>select tb3_section, count(1) from ( 
  select 
    tb3_mid, 
    substring_index(
      substring_index(tb3_section, ',', n), 
      ',', 
      -1
    ) as tb3_section  from table3 
  join numbers
    on char_length(tb3_section) 
      - char_length(replace(tb3_section, ',', '')) 
      &gt;= n - 1
) tb3_section_dashboard
group by 1
</code></pre>

<p>This doesn't give me the group count. Just does the split of tb3_section but doesn't give me the correct count and equivalent percentage. Any ideas will be much appreciate it thanks a lot. </p>

<p><strong>LATEST UPDATE</strong></p>

<p>First of all, I would like to thanks @eggyal for pointing me to the right direction and @Shadow for despise knowing that I was not taking the best approach, he came up with a quick fix to my problem. I managed to change the approach and removed the comma delimited values from table3. Instead now I add multiple rows for each new value (and added a constraint to avoid duplicates).</p>

<p>Now <strong>table3</strong> looks like:</p>

<pre><code>Sample Data:
--------------
1   234   alpha
2   234   beta
3   234   gama
4   234   delta
5   235   alpha
</code></pre>

<p>Here is the query I have taken from @shadow sample:</p>

<pre><code>SELECT t1.tb1_name, COUNT(t3.tb3_section) AS no_per_group,
COUNT(t3.tb3_section) / t4.no_of_groups AS percentage 
FROM t1 left
JOIN t2 ON t1.tb1_id=t2.tb2_tb3_id 
INNER JOIN t3 ON t2.tb2_sector=t3.tb3_section&gt;0 
JOIN (SELECT COUNT(*) AS no_of_groups
       FROM t3 INNER JOIN t2 ON t2.tb2_sector=t3.tb3_section&gt;0) t4 
GROUP BY t1.tb1_name
</code></pre>

<p>Instead of using <strong>find_in_set</strong> now I use <strong>=</strong> to match the exact value. 
Now I get something like the following but the percentage looks odd and I miss a group that doesn't have a match:</p>

<pre><code>Name        no_per_group     percentage
-----       -------------    ----------
group1      2                0.1053
group3      3                0.1579
group4      3                0.1579
group5      3                0.1579
</code></pre>

<p>Although still I need something like:</p>

<pre><code>Name    Count     %
------  -----    -----
group1   1       25%
group2   3       75%
group3   0       0%
group4   0       0%
group5   0       0%
</code></pre>

<p>Notice that if there is no match in a group, I still need to show that group. 
Because I have thousands of records which are different from each other, I need to add another condition: <em>where tb3_mid=234</em> . Likes this, the results are using to tb3_mid. </p>