SQL query - counting number of yes answers for group of questions
I have a table for a survey related application that has 5 questions all requiring Yes(1) or No (0) answers. The table design is as follows:
CREATE TABLE score (
project_id int NOT NULL,
resp_id int NULL,
q1 int,
q2 int,
q3 int,
q4 int,
q5 int,
);
Sample data:
project_id resp_id q1 q2 q3 q4 q5
1 86 1 1 1 1 1
1 114 1 1 1 1 1
1 118 0 1 1 1 0
2 154 1 1 0 1 1
2 178 1 1 1 0 1
3 182 1 0 1 1 1
3 190 1 1 1 0 1
3 208 1 1 1 1 1
3 300 1 1 1 0 0
3 329 1 1 开发者_JAVA技巧1 1 1
What I need to do is write a query that will generate a report where I breakdown by project_id, the number of respondents that provided 0 "yes" answers (0 out of 5), 1 "yes" answer (1 out of 5), 2 "yes" answers (2 out of 5), etc. In other words, how many respondents answers yes to all questions, yes to 4 out of 5 questions, etc.
Doable? Any suggestions are appreciated :)
SELECT
project_ID,
q1 + q2 + q3 + q4 + q5 AS NumYesAnswers,
COUNT(*) AS NumResponses
FROM
score
GROUP BY
project_ID, q1 + q2 + q3 + q4 + q5
EDIT
I'd add this as a comment to Lobo's answer but the formatting wouldn't work. You can achieve the same effect a little more gracefully with a PIVOT
:
SELECT
Project_ID, "0" AS NoToAll, "1", "2", "3", "4", "5"
FROM
(
SELECT Project_ID, Q1 + Q2 + Q3 + Q4 + Q5 AS NumYeses, COUNT(*) AS Answers
FROM Score
GROUP BY Project_ID, Q1 + Q2 + Q3 + Q4 + Q5
) AS X
PIVOT
(SUM(Answers) FOR NumYeses IN ("0", "1", "2", "3", "4", "5")) AS Y
Jon of all Trades' answer is good. In addition, use a CTE (Common Table Expression) if you don't want to have to repeat the sum statement.
With
ProYes as (
select project_id,
q1 + q2 + q3 + q4 + q5 as NumYes
from score)
select project_id, NumYes, COUNT(*) as NumAnswers
from ProYes
group by project_id, NumYes
-- Be careful about NULL fields(the following is assuming that the q* are NOT NULL fields)
SELECT project_id,
SUM(allzeroes) AS NoToAll, SUM(onlyone) AS YesToOne, SUM(two) AS YesToTwo, SUM(three) AS YesToThree, SUM(four) AS YesToFour, SUM(five) AS YesToAll
FROM (
SELECT project_id,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 0 THEN COUNT(*) ELSE 0 END) AS allzeroes,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 1 THEN COUNT(*) ELSE 0 END) AS onlyone,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 2 THEN COUNT(*) ELSE 0 END) AS two,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 3 THEN COUNT(*) ELSE 0 END) AS three,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 4 THEN COUNT(*) ELSE 0 END) AS four,
(CASE WHEN (q1 + q2 + q3 + q4 + q5) = 5 THEN COUNT(*) ELSE 0 END) AS five
FROM score
GROUP BY project_id, (q1 + q2 + q3 + q4 + q5) ) temp_table
GROUP BY project_id
PS: Please correct the sample data header; I can't edit your question. Thanks!
Created a stored procedure and set the values as variables then you can pass them using a select statement within the sproc and conditionally with the where clause.
精彩评论