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:359810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
