Sorry to take so long to respond.
I didn't even explain myself well the solution was:
SELECT
orderedItems.orderID,
sum(items.itemPrice * orderedItems.itemQty) as
theSubTotal
FROM orderedItems, items
WHERE
orderedItems.orderID=#orderID# AND
orderedItems.itemID=items.itemID
GROUP BY orderedItems.orderID
Thanks all. There is a point were walking away from the laptop is better
ROI than spinning your wheels.
Gil
-----Original Message-----
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Monday, May 23, 2005 10:26 AM
To: CF-Talk
Subject: Re: adding subtotals
Thanks, Dave - I barely use Oracle, but knowing that is bound to save
me some research next time I do!
-Joe
On 5/23/05, Dave Carabetta <[EMAIL PROTECTED]> wrote:
> 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.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207712
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
