On 5/23/05, Joe Rinehart <[EMAIL PROTECTED]> wrote: > 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 >
And Oracle has the equivalent analytical functions (which have saved me tons of coding/hacking). SELECT purchaserid, itemid, sum(itemPrice) as TheTotal FROM itemsPurchased GROUP BY ROLLUP ( purchaserid, itemid ) Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207416 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
