MySQL subquery and bracketing

Question

Here are my tables

respondents:

field          sample value
respondentid :  1
age          :  2
gender       :  male

survey_questions:

id           :  1
question     :  Q1
answer       :  sample answer

answers:

respondentid :  1
question     :  Q1
answer       :  1 --id of survey question

I want to display all respondents who answered the certain survey, display all answers and total all the answer and group them according to the age bracket.

I tried using this query:

SELECT
res.Age,
res.Gender,
answer.id,
answer.respondentid,
SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS  males,
SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females,
CASE
    WHEN res.Age < 1 THEN 'age1'
    WHEN res.Age BETWEEN 1 AND 4 THEN 'age2'
    WHEN res.Age BETWEEN 4 AND 9 THEN 'age3'
    WHEN res.Age BETWEEN 10 AND 14 THEN 'age4'
    WHEN res.Age BETWEEN 15 AND 19 THEN 'age5'
    WHEN res.Age BETWEEN 20 AND 29 THEN 'age6'
    WHEN res.Age BETWEEN 30 AND 39 THEN 'age7'
    WHEN res.Age BETWEEN 40 AND 49 THEN 'age8'
    ELSE 'age9'
END AS ageband
FROM Respondents AS res
INNER JOIN Answers as answer ON answer.respondentid=res.respondentid
INNER JOIN Questions as question ON answer.Answer=question.id
WHERE answer.Question='Q1' GROUP BY ageband ORDER BY res.Age ASC

I was able to get the data but the listing of all answers are not present. Do I have to subquery SELECT into my current SELECT statement to show the answers? I want to produce something like this:

ex: # of Respondents is 3 ages: 2,3 and 6

Question: what are your favorite subjects?

Ages 1-4:
      subject 1:     1
      subject 2:     2
      subject 3:     2

total respondents for ages 1-4 : 2

Ages 5-10:
      subject 1:     1
      subject 2:     1
      subject 3:     0

total respondents for ages 5-10 : 1

Answer 1

SELECT
res.Age,
res.Gender,
answer.id,
answer.respondentid,
SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS  males,
SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females,
group_concat(answer.answer separator '\n') answers
CASE
    WHEN res.Age < 1 THEN 'age1'
    WHEN res.Age BETWEEN 1 AND 4 THEN 'age2'
    WHEN res.Age BETWEEN 4 AND 9 THEN 'age3'
    WHEN res.Age BETWEEN 10 AND 14 THEN 'age4'
    WHEN res.Age BETWEEN 15 AND 19 THEN 'age5'
    WHEN res.Age BETWEEN 20 AND 29 THEN 'age6'
    WHEN res.Age BETWEEN 30 AND 39 THEN 'age7'
    WHEN res.Age BETWEEN 40 AND 49 THEN 'age8'
    ELSE 'age9'
END AS ageband
FROM Respondents AS res
INNER JOIN Answers as answer ON answer.respondentid=res.respondentid
INNER JOIN Questions as question ON answer.Answer=question.id
WHERE answer.Question='Q1' GROUP BY ageband ORDER BY res.Age ASC;

You have to set the group_concat_max_len system variable to a higher value:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

Depending on your platform, you should also replace the separator from '\n' to char(13) or char(10), or ' < b r > '.