Join mysql query with multiple counts

Question

My tables:

tournaments

 _______________________________________
| id |        name          | date      |
|---------------------------|-----------|
| 12 | mytournament         | 2016-01-01|
|---------------------------|-----------|

players

 ________________________
| idt |   name      | cat |
|-------------------|-----|
|  12 | john        |  A  |
|  12 | bobby       |  A  |
|  12 | anna        |  B  |
|  12 | Fritz       |  B  |
|  12 | george      |  C  |
|-------------------|-----|

I would need a query that returns this:

id | name | date | nr of cat A | nr of cat B | nr of cat C 

12 | mytournament | 2016-01-01 | 2 | 2 | 1

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

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

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.

any help appreciated. Thanks

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

You want a classic pivot table. Therefore your statement should look something like this:

SELECT t.id, t.name, t.date,
       COUNT(
            CASE 
                WHEN p.`cat`='A' 
                THEN 1 
                ELSE NULL 
            END
       ) AS 'countA',
       COUNT(
            CASE 
                WHEN p.`cat`='B' 
                THEN 1 
                ELSE NULL 
            END
       ) AS 'countB',
       COUNT(
            CASE 
                WHEN p.`cat`='C' 
                THEN 1 
                ELSE NULL 
            END
       ) AS 'countC'
FROM tournaments t 
INNER JOIN players p ON t.id = p.idt
GROUP BY t.id;

Have a look at this Stack overflow question and its answer for more details.

edited May 23 '17 at 11:53

Community

1
1

answered Dec 13 '16 at 19:51

Bjoern

15,934
4
43
48

1

`NULL` is the default `ELSE` for `CASE` statement, no need to include it. – Juan Carlos Oropeza Dec 13 '16 at 19:53
Very true, I usually use it in answers like this for clarification. – Bjoern Dec 13 '16 at 19:54
works great, except for an error to correct. after the 3 when there should be p.`cat` and not t.`cat`. thank you! – sharkyenergy Dec 13 '16 at 19:58
Sorry for the typo, I've adjusted my answer accordingly. – Bjoern Dec 13 '16 at 21:21

Join mysql query with multiple counts

1 Answers1