CODEWITHSUNDEEP
mysqlsqlcoldfusiongroup-by
Suleman khan
Suleman khan

Reputation: 1068

rows gets multiplied because of group by

I am querying several tables to get the count of each answer of a question. here what i am doing is for taking out survey we may ask questions with some answer options upon completion of survey we need get the statistics of a survey then we need to count the count of each answer of the question.

Here is my query.

SELECT s.NAME AS surveyname,
    COUNT(r.answer_id) AS totalAnswer,
    q.id AS questionid,
    q.question AS question,
    a.answer AS answer,
    COUNT(r.textbox) AS totalTextbox,
    COUNT(r.textboxmulti) AS totalTextboxmulti,
    qt.template AS template,
    s.NAME AS surveyname,
    COUNT(r.other) AS other
FROM surveys s
INNER JOIN survey_results AS sr
    ON s.id = sr.survey_id
INNER JOIN results AS r
    ON sr.id = r.surveyresults_id
INNER JOIN questions AS q
    ON r.question_id = q.id
INNER JOIN questiontypes AS qt
    ON q.questiontype_id = qt.id
LEFT JOIN answers AS a
    ON r.answer_id = a.id
WHERE s.id = < cfqueryparam cfsqltype = "cf_sql_integer" value = "#arguments.surveyid#" >
GROUP BY q.id,
    a.id
ORDER BY a.rank

this query working fine exactly what i want. But the problem is while displaying the result on the views what it does is questions gets multiplied with number of answers though I am using cfoutout attribute group with column name questionid. can anyone help me out how can i prevents the multiplication of questions with the number of answers ?

this is the way I am displaying the survey result

<cfoutput query="rc.data.questions" group="questionid">
   <cfswitch expression="#rc.data.questions.template#">
      <cfcase value="multiplechoice">
         <table class="table table-striped table-hover">
            <thead>
               <tr>
                  <th width="50%">#rc.data.questions.question#</th>
                  <th></th>
                  <th>
                     <div class="center">Response Count</div>
                  </th>
               </tr>
            </thead>
            <cfoutput>
               <tbody>
                  <tr>
                     <td width="60%">#rc.data.questions.answer#</td>
                     <td>
                        <div class="center">#rc.data.questions.totalanswer#</div>
                     </td>
                  </tr>
            </cfoutput>
            <cfif structKeyExists(rc.data.questions, "totalother") AND rc.data.questions.template EQ 'multiplechoiceother' OR rc.data.questions.template EQ 'multiplechoicemultiother'> 
            <tr>
            <td><a href="#buildurl(action='survey.text_other',querystring='id=#questionid#')#" target="_blank">View other Text answers</a></td>
            <td><div class="center">#rc.data.questions.totalother#</div></td>
            </tr>
            </cfif>
            </tbody>
         </table>
         </table>
      </cfcase>
   </cfswitch>
</cfoutput>

Upvotes: 1

Views: 133

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

The order of the fields in the order by clause has to match the order in which you want to use the group attribute of cfoutput. If you want to do this:

<cfoutput query="SomeQuery" group="field1">
    #data for this grouping#
    <cfoutput group="field2">
        #data for this grouping#
        <cfoutput>
            #ungrouped data#
        </cfoutput>
    </cfoutput>
</cfoutput>

then your query has to end with:

order by field1, field2, other_fields_if_appropriate

Upvotes: 2

Related Questions