Force MySQL to return a result for all option within IN

Question

I have a simple MySQL statement:

SELECT q1, COUNT(q1) FROM results WHERE q1 IN ('1','2','3');

Currently there are only results for 1 and 3 - results are:

1 = 6
3 = 7

But what I need is for MySQL to bring back a result for 1,2 and 3 even though 2 has no data, as this:

1 = 6
2 = 0
3 = 7

Any ideas?

Answer 1

This is tricky because no rows match your value (2), they cannot be counted.

I would solve this by creating a temp table containing the list of values I want counts for:

CREATE TEMPORARY TABLE q ( q1 INT PRIMARY KEY );
INSERT INTO q (q1) VALUES (1), (2), (3);

Then do an OUTER JOIN to your results table:

SELECT q.q1, COALESCE(COUNT(*), 0) AS count
FROM q LEFT OUTER JOIN results USING (q1)
GROUP BY q.q1;

This way each value will be part of the final result set, even if it has no matching rows.

---

Re comment from @Mike Christensen:

MySQL doesn't support CTE's, in spite of it being requested as far back as 2006: http://bugs.mysql.com/bug.php?id=16244

You could do the same thing with a derived table:

SELECT q.q1, COALESCE(COUNT(*), 0) AS count
FROM (SELECT 1 AS q1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS q 
LEFT OUTER JOIN results USING (q1)
GROUP BY q.q1;

But this creates a temp table anyway.