Group by yes or no

select q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q13,q14,q15,q16 from yourtable

or, if that's too simplistic  please provide an example of what the input might look like and what you expect the output to be

I like to total the results e.g (5 people answered yes and 5 people answered no for q1 resulting in 50/50% split. I can handle it in a recordset  like below but was hoping it could be done in the sql statement

ql="select * from results"

rs.open sql, conn
if not rs.eof
q1=rs(q1)
if q1="yES "THEN
COUNT1= COUNT1+ 1
ELSE
...

select
sum(case q1 when 'Yes' then 1 else 0 end) / cast(count(q1) as decimal(10,2)) as Q1PCT
from MyTable

but your data is not normalized, it should be
respondent_id, question_id, response

then your query would be

select
sum(case response when 'Yes' then 1 else 0 end) / cast(count(response as decimal(10,2)) as Q1PCT,
question_id
from mytable
group by question_id

Then you can use the second example above:

SELECT
  sum(if(q1='yes',1,0)) as q1_yes,
  sum(if(q1='no',1,0)) as q1_no,
  sum(if(q2='yes',1,0)) as q2_yes,
  sum(if(q2='no',1,0)) as q2_no,
  sum(if(q3='yes',1,0)) as q3_yes,
  sum(if(q3='no',1,0)) as q3_no,
  sum(if(q4='yes',1,0)) as q4_yes,
  sum(if(q4='no',1,0)) as q4_no
  /* repeat this for each question */
FROM results

Select all Open in new window