As I recall, when you use aggregate functions and a group by clause, any columns referenced in the select clause outside the aggregate function, must be included in the group by clause.
Realize that columns that appear in the where and join clauses do *not* need to appear in the Select clause It really helps to have an interactive query tool. If you can't get you want with SQL grouping, you probably can with CF grouping (cfoutput) Dick On Sunday, August 18, 2002, at 07:05 PM, Eric Hoffman wrote: > 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 >> >> >> >> > > ______________________________________________________________________ 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
