Well, the problem is that I'm actually showing a total of items which is sum of items per order, I suppose an example is in order:
SELECT to_char(order_date,'YYYY-MM-DD') as totaldate, sum(oi.quantity) as totalqty, sum(oi.unit_price) as totalsold FROM orderitems oi, orderheader oh, product p WHERE oi.orderno = oh.orderno AND p.productid = oi.productid AND to_char(order_date,'YYYY-MM') = '#order_month#' <CFIF LEN(ordersource)>and oh.ordersource = '#ordersource#'</CFIF> GROUP BY to_char(order_date,'YYYY-MM-DD') Since I have to sum the orderitems by quantity and dollar amount, I've "wasted" my grouping - now I can't group by status... > Use SQL to do the grouping, it is a lot easier. > > Here is an example that will work on SQL Server: > > SELECT count(order_id), date_shipped, status > FROM ORDERS > GROUP BY date_shipped, status > ORDER BY date_shipped, status > > Manipulate the SQL to your particular situation, and good luck. > Mike > > -----Original Message----- > From: Tony Schreiber [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 04, 2001 11:53 PM > To: CF-Talk > Subject: "2D" Query Grouping > > > I have several instances where I need to group by more than one condition. > > For example, I create a sales report where I show BY DAY the total of > orders and their status (shipped, cancelled, etc). I'm already grouping by > day to get the daily totals, how can I also group by status? As it is, I > do a separate day-grouped query for each status and output each one into a > structure that I then display (and graph)... > > Another example is inventory. I have products of varying conditions in > different types of locations. I want to show a table of condition/location > like this: > > Online Overstock > New 56 355 > Used 34 234 > Crap 34 39 > > I can do one query and group by condition (I have to sum the quantity from > multiple inventory locations to get that one number, that's where my group > function goes). Then I have to do another query to group by type of > location. But that gives me separate totals like this: > > Online 124 > Overstock 628 > > and > > New 411 > Used 268 > Crap 73 > > Any ideas? > > Tony Schreiber, Senior Partner Man and Machine, Limited > mailto:[EMAIL PROTECTED] http://www.technocraft.com > > http://www.simplemessageboard.com ___Free Forum Software for Cold Fusion > http://www.is300.net ___________The Enthusiast's Home of the Lexus IS300 > http://www.digitacamera.com ______________DigitA Camera Scripts and Tips > http://www.linklabexchange.com _____________Miata Link ECU Data Exchange > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
