CODEWITHSUNDEEP
mysqlgroup-by
Luke
Luke

Reputation: 3

How do I group a MySQL query by two different columns?

The question I am working on is:

Provide the average answer for questions in broken down by Province and then by Question. You must include the province and the question as well as the average.

The example output is meant to look something like this:

 Ontario - Do you like blak? - 3.4
 Ontario - Do you like flah? - 2.3
 Ontario - Do you like rahhggg? - 4.8
 Alberta - Do you like flah? - 2.3
 Alberta - Do you like rahhggg? - 4.8

So far I have tried to list them by question with the average but I don't know how to group it by the province first. I think it's a JOIN but I'm not sure how to lay it out. What I have:

SELECT survey_question_id, AVG(survey_response)
FROM survey_responses
JOIN survey_responders 
ON survey_responses.id = survey_responders.id
WHERE survey_responders.province='Ontario'
GROUP BY survey_question_id;

Upvotes: 0

Views: 60

Answers (2)

Smog
Smog

Reputation: 604

SELECT province,survey_question_id, AVG(survey_response)
FROM survey_responses
JOIN survey_responders 
ON survey_responses.id = survey_responders.id
WHERE survey_responders.province='Ontario'
GROUP BY province,survey_question_id;

if you need all the provinces just remove the Where like this

SELECT province,survey_question_id, AVG(survey_response)
FROM survey_responses
JOIN survey_responders 
ON survey_responses.id = survey_responders.id
GROUP BY province,survey_question_id;

Upvotes: 1

Richthofen
Richthofen

Reputation: 2086

The GROUP BY statement within MySQL determines how the query engine aggregates the data. You are currently grouping only by survey_question_id. since the ids will all be different, you will never get a aggregate in your results.

I would suggest changing your group by to:

GROUP BY survey_responders.province, survey_responses.questiontitle

and make sure you edit your SELECT statement appropriately.

Upvotes: 1

Related Questions