Counting values in table joined several times

I have a problem counting a table joined several times.

The question table :

+----+----------+
| id | question |
+----+----------+
|  1 | Foo?     |
+----+----------+

The answer one :

+----+-------------+--------+
| id | question_id | choice |
+----+-------------+--------+
|  1 |           1 |      1 |
|  2 |           1 |      1 |
|  3 |           1 |    开发者_开发问答  1 |
|  4 |           1 |      2 |
|  5 |           1 |      3 |
|  6 |           1 |      3 |
+----+-------------+--------+

The expected result :

+----------+-------+-------+-------+
| question | num_1 | num_2 | num_3 |
+----------+-------+-------+-------+
| Foo?     |     3 |     1 |     2 |
+----------+-------+-------+-------+

The (failing) query and its result :

SELECT
    q.question AS question,
    COUNT(a1.id) AS num_1,
    COUNT(a2.id) AS num_2,
    COUNT(a3.id) AS num_3
FROM
    question q
    LEFT JOIN answer a1 ON a1.question_id = q.id AND a1.choice = 1
    LEFT JOIN answer a2 ON a2.question_id = q.id AND a2.choice = 2
    LEFT JOIN answer a3 ON a3.question_id = q.id AND a3.choice = 3
GROUP BY
        q.id

+----------+-------+-------+-------+
| question | num_1 | num_2 | num_3 |
+----------+-------+-------+-------+
| Foo?     |     6 |     6 |     6 |
+----------+-------+-------+-------+

I don't understand why I get this result. Can you help me?

---

Because choice = 1 gives 3 rows, choice = 2 gives 1 row, choice = 3 gives 2 rows and 1 * 2 * 3 = 6. if you remove the group by and aggregates and look at the results it should be clear. You can use

SELECT
    q.question AS question,
    COUNT(CASE WHEN a.choice = 1 THEN 1 END) AS num_1,
    COUNT(CASE WHEN a.choice = 2 THEN 1 END) AS num_2,
    COUNT(CASE WHEN a.choice = 3 THEN 1 END) AS num_3
FROM
    question q
    LEFT JOIN answer a ON a.question_id = q.id AND a.choice IN (1,2,3)
GROUP BY
        q.id,
        q.question

---

If you run your query without the counts and grouping, you'll see you get results like this:

+------+------+------+------+
| q    | num1 | num2 | num3 |
+------+------+------+------+
| foo  |    1 |    4 |    5 |
| foo  |    1 |    4 |    6 |
| foo  |    2 |    4 |    5 |
| foo  |    2 |    4 |    6 |
| foo  |    3 |    4 |    5 |
| foo  |    3 |    4 |    6 |
+------+------+------+------+

As expected, 6 rows, so each aliased field will give you a count of 6. Martin Smith's got the right answer above.