MySQL GROUP BY and JOIN
- by christian
Guys what's wrong with this SQL query:
$sql = "SELECT
res.Age,
res.Gender,
answer.*,
$get_sum,
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
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 res.Age ORDER BY res.Age ASC";
the $get_sum is an array of sql statement derived from another table:
$sum[]= "SUM(CASE WHEN answer.Answer=".$db->f("id")." THEN 1 else 0 END) AS item".$db->f("id");
$get_sum = implode(', ', $sum);
the query above return these values:
Age: 20
item1 0
item2 1
item3 1
item4 1
item5 0
item6 0
Subtotal for Age 20 3
Age: 24
item1 2
item2 2
item3 2
item4 2
item5 1
item6 0
Subtotal for Age 24 9
It should return:
Subtotal for Age 20 1
Subtotal for Age 24 2
In my sample data there are 3 respondents 2 are 24 yrs of age and the other one is 20 years old.
