python查找客户总金额,SQL查询，用于统计每个客户的订单数和总金额

I have two tables

Order with columns:

OrderID,OrderDate,CID,EmployeeID

And OrderItem with columns:

OrderID,ItemID,Quantity,SalePrice

I need to return the CustomerID(CID), number of orders per customer, and each customers total amount for all orders.

So far I have two separate queries. One gives me the count of customer orders....

SELECT CID, Count(Order.OrderID) AS TotalOrders

FROM [Order]

Where CID = CID

GROUP BY CID

Order BY Count(Order.OrderID) DESC;

And the other gives me the total sales. I'm having trouble combining them...

SELECT CID, Sum(OrderItem.Quantity*OrderItem.SalePrice) AS TotalDollarAmount

FROM OrderItem, [Order]

WHERE OrderItem.OrderID = [Order].OrderID

GROUP BY CID

I'm doing this in Access 2010.

解决方案

You w