Are you looking for a count of responses, or the actual value, though? In my test I used a bit datatype on the column so count() is all I needed for my example. If youve got a text value of YES or something else, though, you could use a sub-function or a conditional inside count to convert that to a bit value for your purposes. Either way, the end result is that you have one query, grouped by question with your response counts for each question. Then you only have to loop the query once instead of keeping track of iterations, cases and conditionals.
Jon On Dec 10, 2014, at 9:15 PM, Torrent Girl <[email protected]> wrote: > > disagree is not an actual column it is a value. > > > > I could be wrong, but it seems like you might be making it harder than >> it needs to be from the query side, if you are looking for totals of >> responses by question. You might be able to just use a GROUP BY >> clause to handle it. >> >> SELECT questions.id,questions.question, >> count(r.disagree) as disagreed, /*Disagreed Count*/ >> count(r.agree) as agreed, /*Agreed Count*/ >> count(r.neither) as neutral, /*Neutral Count*/ >> count(r.strongly_agree) as strongly_agreed, /*SA Count*/ >> count(r.strongly_disagree) as strongly_disagreed, /*SD Count*/ >> (SELECT count(*) FROM responses r2 /*No Response Count*/ >> where question_id = questions.id >> AND r2.agree IS NULL >> AND r2.disagree IS NULL >> AND r2.neither IS NULL >> AND r2.strongly_agree IS NULL >> AND r2.strongly_disagree IS NULL) as no_response >> FROM questions >> INNER JOIN responses r on r.question_id = questions.id >> GROUP by questions.id,questions.question >> >> Note the following was tested on MySQL and Im sure theres an SQL >> ninja that could probably find a better way to aggregate the >> sub-select. >> >> HTH, >> Jon >> >> >> >> On Dec 10, 2014, at 7:32 PM, Torrent Girl <[email protected]> >> wrote: >> >>> >>> Sorry let me try that again...what I meant was.... >>> >>> >>> Not quite. The value returned is a COUNT of the number of each >> response (sorry I should have said that earlier. So, if no one >> answered "disagree" it is not returned, but in the loop I want it >> represented as 0. >>> >>> >>> :) >>> >>> >>>> >>>> >>>> >>>>> If I understand correctly >>>>> >>>>> Select response1 from [table] >>>>> >>>>> The resulting Query can equal 'response1' == 'Agree' or 'Disagree' >> >>>> or >>>>> 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly >>>> Disagree' >>>>> >>>>> Then you loop over the results from the query and (I am guessing >>>> here) you >>>>> want to place an 'X' in the box under the correct header? >>>>> >>>>> Simplest I can think of would be >>>>> >>>>> <cfoutput query="[qname]"> >>>>> <tr> >>>>> <cfswitch expression="#[qname].response1#"> >>>>> <cfcase value="Agree"> >>>>> <td>X</td><td></td><td></td><td></td><td></td> >>>>> </cfcase> >>>>> <cfcase value="Disagree"> >>>>> <td></td><td>X</td><td></td><td></td><td></td> >>>>> </cfcase> >>>>> <cfcase value="Neither Agree nor Disagree"> >>>>> <td></td><td></td><td>X</td><td></td><td></td> >>>>> </cfcase> >>>>> <cfcase value="Strongly Agree"> >>>>> <td></td><td></td><td></td><td>X</td><td></td> >>>>> </cfcase> >>>>> <cfcase value="Strongly Disagree"> >>>>> <td></td><td></td><td></td><td></td><td>X</td> >>>>> </cfcase> >>>>> </cfswitch> >>>>> </tr> >>>>> </cfoutput> >>>>> >>>>> >>>>> ---------------------------------- >>>>> William Seiter >>>>> >>>>> I am hoping I can explain this correctly. >>>>> >>>>> I have a table which houses responses to a survey. >>>>> >>>>> The columns can have any of the following values: >>>>> >>>>> Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly >> >>>> Disagree >>>>> >>>>> I have a table with the headers equal to all of the values above. >>>>> >>>>> I have a 2nd row that loops through the query and adds a cell in >> the >>>> row for >>>>> each value: >>>>> >>>>> EX: <td>#query.value#</td> >>>>> >>>>> Here is my problem: >>>>> >>>>> There are times when not all of the values exists, so I don't have >> >>>> enough of >>>>> the dynamic cells in the row. EX: I have a header called "Agree" >> but >>>> the >>>>> value "agree" is not returned from the query so it messes up the >> row. >>>> >>>>> >>>>> My question is how can I create a "fake" column from the query OR >> >>>> create an >>>>> empty cell in the row if a value doesn't exist? >>>>> >>>>> NOTE: The values don't exist so I can "test for existence" if that >> >>>> makes >>>>> sense. >>>>> >>>>> Thanks in advance. >>> >>> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
