Count and group by query for counting rows in database table based on 3 to 4 conditions

Question

I am running a market survey and all the survey data is saved in the database. I need a query for counting the number of rows in which option "1" is selected for question "1", option "2" for question "1" and so on for all questions and options… I need to specify few conditions here, I have to match distinct ID's of 3 tables and display the result for each particular ID.

Answer 1

I think you're going to have to do it the hard way:

SELECT COUNT(`keyfield`)
 WHERE `column` = '1'

and,

SELECT COUNT(`keyfield`)
 WHERE `column` = '2'

Else you'll need a script to run through all the iterations of the data/options. What platform are you on?

Answer 2

The basis for a query would be something like this:

SELECT q.question, a.answer, COUNT(a.answer)
  FROM questions q
  LEFT JOIN answers a ON (q.id = a.question_id)
 GROUP BY q.id, a.id
 ORDER BY q.id, a.id

You could add the necessary conditions in a WHERE clause.

Answer 3

assuming table

survey (question, answer)

you can simply do

select 
    question, answer, count(*) as numberOrResponses
from
    survey
group by
    question, answer
order by
    question, answer

that will give you results like:

'question 1', 'answer 1', 10
'question 1', 'answer 2', 2 
... etc

Of course if your table is normalised just use the proper joins in the from part

Answer 4

SELECT 
    question,
    sum(CASE (answer1) WHEN "true" THEN 1 WHEN "false" THEN 0 END) as answer1,
    sum(CASE (answer2) WHEN "true" THEN 1 WHEN "false" THEN 0 END) as answer2
FROM 
    survey
group by 
    question

I hope this is what you want !