x
Solved

# Group by yes or no

Posted on 2008-10-08
Medium Priority
213 Views
I have fields q1-q16 that are either yes or no. How can a pull the results into a recordset using an sql statement.  Case statement?
0
Question by:cseink

LVL 74

Expert Comment

ID: 22670816
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
0

LVL 39

Accepted Solution

Roger Baklund earned 200 total points
ID: 22671008
It's not very clear what you are trying to achieve. In general, you would use the SELECT statement to get the content of a table.

SELECT * FROM MyTable

This will pull the results into a recordset.

The title of your question is "Group by yes or no", so I am guessing that you wish to accumulate the rows of the table in some way. Some examples are provided below, if I am not answering your question, please provide some more information about how the result is supposed to look like.
``````/* count of yes/no (and unanswered) for question 1: */
SELECT q1,count(*) FROM MyTable

/* Sum of yes/no replies to questions q1-q4: */
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
FROM MyTable
``````
0

Author Comment

ID: 22671023
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
...
0

LVL 19

Expert Comment

ID: 22671180
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

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

0

LVL 39

Expert Comment

ID: 22671227
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
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.