MySQL GROUP BY and JOIN

Posted by christian on Stack Overflow See other posts from Stack Overflow or by christian
Published on 2010-05-11T03:43:00Z Indexed on 2010/05/11 3:44 UTC
Read the original article Hit count: 401

Filed under:

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.

© Stack Overflow or respective owner

Related posts about mysql