How grouping and totaling are done into three tables using JOIN
- by text
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:
$sql = "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. What's wrong with my query.
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
