Okay, I see where you are going...so do all the sums, avg blah where in statelist...final question, when using aggregate functions like this, we have all these extra columns not being used for *necessarily* for the joins or selection of data, just for output....they all have to be accounted for in a group by function somehow if we have mixed select and aggregate functions...that still sticks with me...can't leave them unattended so to speak?
Regards, Eric J Hoffman DataStream Connexion www.datastreamconnexion.com Delivering Creative Data Solutions -----Original Message----- From: Dick Applebaum [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 18, 2002 8:31 PM To: CF-Talk Subject: Re: Unholy query mess 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 > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
