title: Mysql how to count column values and create corresponding column names
body: <p>Mysql Version <code>mysql  Ver 8.0.29-0</code></p>
  <h3>Inital table</h3>
  <p><strong>tableA</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>name</th>
  <th>uid</th>
  <th>count</th>
  <th>other_columns</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>first</td>
  <td>a</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>first</td>
  <td>b</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>first</td>
  <td>c</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>first</td>
  <td>d</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>second</td>
  <td>e</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>second</td>
  <td>f</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>second</td>
  <td>g</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>second</td>
  <td>h</td>
  <td>1</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>tableB</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>uid</th>
  <th>type</th>
  <th>other_columns</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>a</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>b</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>c</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>d</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>e</td>
  <td>2</td>
  <td></td>
  </tr>
  <tr>
  <td>f</td>
  <td>3</td>
  <td></td>
  </tr>
  <tr>
  <td>g</td>
  <td>3</td>
  <td></td>
  </tr>
  <tr>
  <td>h</td>
  <td>3</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div><h3>try</h3>
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT a.name, b.type, SUM(a.count)
  FROM (
      SELECT `name`, `uid`, `count`
      FROM tableA
  ) a
  INNER JOIN tableB b
  ON a.uid = b.uid
  GROUP BY a.name, b.type
  </code></pre>
  <p>get</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>name</th>
  <th>type</th>
  <th>count</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>first</td>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>first</td>
  <td>2</td>
  <td>3</td>
  </tr>
  <tr>
  <td>second</td>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>second</td>
  <td>3</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div><h3>expect</h3>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>address</th>
  <th>count1</th>
  <th>ratio1</th>
  <th>count2</th>
  <th>ratio2</th>
  <th>count3</th>
  <th>ratio3</th>
  <th>total</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>first</td>
  <td>3</td>
  <td>0.5</td>
  <td>3</td>
  <td>0.5</td>
  <td>0</td>
  <td>0</td>
  <td>6</td>
  </tr>
  <tr>
  <td>first</td>
  <td>0</td>
  <td>0</td>
  <td>1</td>
  <td>0.2</td>
  <td>4</td>
  <td>0.8</td>
  <td>5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>how can I make it?</p>
  <p>Thank you</p>