Solved

# Group by yes or no

Posted on 2008-10-08
198 Views
Last Modified: 2012-05-05
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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
5 Comments

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 50 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

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

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.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll

#### 617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.