Customer Total?

[milton747]

Hi Folks,

The CustomerTotal is always the same as the GrandTotal.
I thought my syntax would provide the total for ALL orders for each Customer ID.
Any suggestions?

Thank U.

TABLES.....

Table_Orders
* OrderID
* CustID

Table_Customers
* CustID

Table_Items
* OrderID
* itemcode
* quantity
* price

---------------------------

SELECT
customers.custID, orders.orderID,
items.quantity, items.pricequote, items.itemcode,
items.orderID, ( items.quantity * items.pricequote ) AS LineTotal,

(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE customers.custID = orders.custID
GROUP BY customers.custID
) AS CustomerTotal,

(SELECT SUM ( items.quantity * items.pricequote )
FROM items
) AS GrandTotal

FROM customers, orders, items
WHERE customers.custID = orders.custID
AND orders.orderID = items.orderID
AND customers.custID < CID

ORDER BY customers.custID, orders.orderID, 7 DESC

-- end --

 

[Remou]

Perhaps:

[tt]SELECT orders.custid,
orders.orderid,
items.quantity,
items.pricequote,
items.itemcode,
([items].[quantity] * [items].[pricequote]) AS linetotal,
ct.custlinetotal,
gt.grandtotal
FROM (SELECT SUM(items.quantity * items.pricequote) AS grandtotal
FROM items) AS gt,
items
INNER JOIN (orders
INNER JOIN (SELECT orders.custid,
SUM((items.[quantity] * items.[pricequote])) AS custlinetotal
FROM orders
INNER JOIN items
ON orders.orderid = items.orderid
GROUP BY orders.custid) AS ct
ON orders.custid = ct.custid)
ON items.orderid = orders.orderid[/tt]