I want to display the count for each of 4 values. This SQL statement won't do it. It only shows counts if they are non zero. I used to think a LEFT JOIN would cause all rows to be listed from the table on the left, but I guess I thought wrong. Now I don't know what LEFT JOIN does.
CREATE TEMPORARY TABLE 4answers(
ans float default NULL
);# MySQL returned an empty result set (i.e. zero rows).
INSERT 4answers
VALUES ( 1 ) , ( 0 ) , (-1), ( 0.6667 ) ;# Affected rows: 4
SELECT ans, COUNT( * )
FROM 4answers A
LEFT JOIN audit_l321 AA ON A.ans = AA.qa_answer_01
WHERE team =11
AND date( dtm_date )
BETWEEN date( '2009-04-01' )
AND date( '2009-04-30' )
GROUP BY ans
Result:
ans COUNT( * )
0.6667 5
1 6
Desired result:
ans COUNT( * )
0.6667 5
1 6
0 0
-1 0
