Dave Watts had asked a question earlier and cc'd me privately. I mistakenly thought you all had this information as you were answering my question. Hopefully you can see why I'm using array's and looking for some way of not having to specify the container of the average for each field. I'm trying to avoid specifying the "AS field_avg" since I don't know how many fields there may be the next time I do this, if that makes sense.
In reading over the responses, it would appear that I've taken an inefficient approach to placing the "Questions" query into an array. I'll be modifying that using one of the approaches presented by Peter, Jochem, or Joseph. -------- Original Message -------- Subject: Re: Query into Array, averages in SQL statement Date: Sun, 21 Apr 2002 13:26:16 -0500 From: John White <[EMAIL PROTECTED]> To: Dave Watts <[EMAIL PROTECTED]> CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> I have two databases, one has the following structure: Questions: qid qtext qname --- -------------------------- ----- 1 patience: q1 2 listening skills q2 etc... Results: survid q1 q2 q3 . . . qXX ------ -- -- -- -- UUid() 1 3 2 XX The complex part is how to get the average of the results to match to the question text. So I came up with this: <cfset QuesArray = ArrayNew(2)> <!--- Get text of first set of questions ---> <cfquery name="GetQuestions" datasource="#application.ds#"> select qid, qtext from #application.QuestionSets# </cfquery> <cfset count = 1> <cfoutput query="GetQuestions"> <cfset QuesArray[#count#][1] = #GetQuestions.qid#> <cfset QuesArray[#count#][2] = #GetQuestions.qtext#> <cfset count = count + 1> </cfoutput> This part works beautifully. And since it is in an array, I can make an array of the average functions from the "Results" table (a 1 x 52 array) match to the question array using the following (once I have the averages into an array off course!) <cfloop index="count" from "1" to "52" step="1"> <td>QuesArray[#count#][1]</td> <td>QuesArray[#count#][2]</td> <td>ResultArray[1][#count#]</td> </cfloop> I'm stumped on creating the ResultArray. I don't want to write the following: select avg(q1) as avg_q1, avg(q2) as avg_q2, ..... Since I may have many more questions next time if that makes sense. If I must, I must. Many thanks for the prompt reply! --JW Dave Watts wrote: >>Snip!<< Some good questions about my original post! :) ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
