Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Problems with SUM in SQL for Orders

0 views
Skip to first unread message

Jason

unread,
Jan 18, 2003, 9:43:29 PM1/18/03
to
I am having problems with the query below. I know I must be doing
something wrong. If the order_detail_t table contains more than 1
record, it adds the ordersubtotal, ordershippingtotal, ordertaxtotal,
and ordertotal again for the # of rows the order detail contains.
Thus, this result totals are wrong.

SELECT
Sum(OD.ProductQuantity) AS Units,
Sum(O.OrderSubTotal) as OrderSubTotal ,
Sum(O.OrderShippingTotal) as OrderShippingTotal ,
Sum(O.OrderTaxTotal) as OrderTaxTotal ,
Sum(O.OrderTotal) as OrderTotal
FROM
Order_T O
JOIN
Order_Detail_T OD ON O.OrderID = OD.OrderID
WHERE
(OrderDate >= '1/18/2003 00:00:00' AND OrderDate <= '1/18/2003
23:59:59')


This one fails too if I want to display a report based on order id.


SELECT
Sum(OD.ProductQuantity) AS Units,
O.OrderID ,
Sum(O.OrderSubTotal) as OrderSubTotal ,
Sum(O.OrderShippingTotal) as OrderShippingTotal ,
Sum(O.OrderTaxTotal) as OrderTaxTotal ,
Sum(O.OrderTotal) as OrderTotal
FROM
Order_T O
JOIN
Order_Detail_T OD ON O.OrderID = OD.OrderID
WHERE (OrderDate >= '1/17/2003 00:00:00' AND OrderDate <= '1/17/2003
23:59:59')
GROUP BY O.OrderID


Any help would be greatly appreciated.

Thank you,
Jason

0 new messages