Join mysql query with multiple counts
<p>My tables:</p>

<p>tournaments</p>

<pre><code> _______________________________________
| id |        name          | date      |
|---------------------------|-----------|
| 12 | mytournament         | 2016-01-01|
|---------------------------|-----------|
</code></pre>

<p>players</p>

<pre><code> ________________________
| idt |   name      | cat |
|-------------------|-----|
|  12 | john        |  A  |
|  12 | bobby       |  A  |
|  12 | anna        |  B  |
|  12 | Fritz       |  B  |
|  12 | george      |  C  |
|-------------------|-----|
</code></pre>

<p>I would need a query that returns this:</p>

<pre><code>id | name | date | nr of cat A | nr of cat B | nr of cat C 

12 | mytournament | 2016-01-01 | 2 | 2 | 1
</code></pre>

<p>I am havint problems with the multiple count... Can't figure out how to join the query.</p>

<pre><code>select t.id, t.name, t.date, count(p.cat) as countA from tournaments t join players p on (t.id = p.idt)
</code></pre>

<p>but I can not figure out how to specify it should only take the count where cat = A and how to add the additional counts for B and C.</p>

<p>any help appreciated. Thanks</p>