问题描述:

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.

I want to total the number of respondents per age.

网友答案:
$sql = "SELECT
    res.Age,
    COUNT(1) AS SubTotalRespondentsByAge
    $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"

You cannot include any columns in the select clause that have a many-to-one relationship with age. I've thus removed the res.gender and answers.* columns. What you want is count(1) of the groups (since you group by res.Age).

相关阅读:
Top