Also, if you're using MS SQL server, try doing ewok's query using the WITH ROLLUP option...by using it with proper grouping, you can have the server get the by-purchaser and overall totals in the query itself. May have to add a column or two to control ordering of NULL values, but, hey, it's MS...
SELECT purchaserid, itemid, sum(itemPrice) as TheTotal FROM itemsPurchased GROUP BY purchaserid, itemid WITH ROLLUP -Joe On 5/20/05, Ewok <[EMAIL PROTECTED]> wrote: > I didn't mean to send that just yet :) > > I assumed you meant adding it in a query versus CF... > > This will get distinct purchaserIDs and their total dollars spent on any all > together > > SELECT purchaserid, sum(itemPrice) as TheTotal > FROM itemsPurchased > group by purchaserid > > This will break the previous results down by items and the total amounts > purchaserID spent on them. > > SELECT purchaserid, itemid, sum(itemPrice) as TheTotal > FROM itemsPurchased > group by purchaserid, itemid > > just add your 'where purchaserid = #purchaserid#' to get a specific one. > > > > > -----Original Message----- > From: Ewok [mailto:[EMAIL PROTECTED] > Sent: Friday, May 20, 2005 10:47 PM > To: CF-Talk > Subject: RE: adding subtotals > > SELECT sum(itemPrice) as TheTotal > FROM itemsPurchased > WHERE purchaserID = '#purchaserID#' > > > -----Original Message----- > From: mayo [mailto:[EMAIL PROTECTED] > Sent: Friday, May 20, 2005 9:15 PM > To: CF-Talk > Subject: adding subtotals > > I'm completely blanking on how to add subtotals. I'll explain clearer > below: > > > I have a table of itemsPurchased that includes itemID itemQty and > itemPrice. > > <!--- simplified ---> > > SELECT itemID, itemQty, itemPrice > FROM itemsPurchased > WHERE purchaserID = '#purchaserID#' > > > Say person A bought > > 3 items at $10 and > 2 items at $20 > > I have no trouble arriving at the subtotals (30 and 40 in this example) > but now I want to add these for 70. > > I can't get this . getting . tired . not thinking straight . > > Thx, > > gil > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207410 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
