Solved

Group by yes or no

Posted on 2008-10-08
5
192 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
Comment
Question by:cseink
5 Comments
 
LVL 73

Expert Comment

by:sdstuber
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

by:
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

Open in new window

0
 

Author Comment

by:cseink
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

by:folderol
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

by:Roger Baklund
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

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now