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.