I think you can do this all within a single SQL query, itself (no loop invoking multiple queries).
SQL "Group By" and "Order By" can be used to group and sort the results. The IN operator of the WHERE clause will allow you to select "any" record which matches any value IN the state or country list. I would get the selection/join logic working first, then add the grouping. HTH Dick On Sunday, August 18, 2002, at 05:53 PM, Eric Hoffman wrote: > Okay, partner brought me this, probably an easy fix, but up to our > elbows and can't find the forest. Please shed some light on where we > have fouled up. > > We are trying to do a report which grabs by state, the sum of orders, > avg order total, and we are trying to unsuccessfully do so...here it is: > (is there a better way to do?) > > <cfloop index="i" list="#statelist#" delimiters=","> > <cfquery name="getrow#i#" datasource="#heinz.dsn#"> > Select customer_orders.orderdate, > customer_orders.bill_id, > AVG(customer_orders.subtotal) AS ORDERAVE, > SUM(customer_orders.subtotal) AS ORDERCOM, > customer_bill.id, > customer_bill.state, > customer_bill.country > FROM customer_orders > LEFT OUTER JOIN customer_bill on customer_orders.bill_id = > customer_bill.id > > WHERE customer_orders.orderdate BETWEEN #start# AND #end# > <cfif form.regional EQ "state"> > AND customer_bill.state = '#listGetAt(statelist,i)#' > <cfelseif form.regional EQ "country"> > AND customer_bill.country = '#listGetAt(countrylist,i)#' > </cfif> > </cfquery> > > </cfloop> > > Regards, > > Eric J Hoffman > DataStream Connexion > www.datastreamconnexion.com > Delivering Creative Data Solutions > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
