SQL查询，用于统计每个客户的订单数和总金额 [英] SQL Query for counting number of orders per customer and Total Dollar amount

问题描述

我有两个桌子 Order带有列:

I have two tables Order with columns:

OrderID,OrderDate,CID,EmployeeID

和OrderItem带有列:

OrderID,ItemID,Quantity,SalePrice

我需要返回CustomerID(CID)，每个客户的订单数以及所有订单的总金额.

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

我正在Access 2010中这样做.

I'm doing this in Access 2010.

推荐答案

您将在其他SQL引擎中使用COUNT(DISTINCT ...):

You would use COUNT(DISTINCT ...) in other SQL engines:

SELECT CID, 
       Count(DISTINCT O.OrderID) AS TotalOrders, 
       Sum(OI.Quantity*OI.SalePrice) AS TotalDollarAmount 
FROM [Order] O
INNER JOIN [OrderItem] OI
  ON O.OrderID = OI.OrderID
GROUP BY CID 
Order BY Count(DISTINCT O.OrderID) DESC

不幸的是，哪个访问不支持.相反，您可以先获取订单金额，然后在确定订单数量之前将它们加入:

Which Access unfortunately does not support. Instead you can first get the Order dollar amounts and then join them before figuring the order counts:

SELECT CID,
       COUNT(Orders.OrderID) AS TotalOrders,
       SUM(OrderAmounts.DollarAmount) AS TotalDollarAmount
FROM [Orders]
INNER JOIN (SELECT OrderID, Sum(Quantity*SalePrice) AS DollarAmount 
      FROM OrderItems GROUP BY OrderID) AS OrderAmounts
  ON Orders.OrderID = OrderAmounts.OrderID
GROUP BY CID
ORDER BY Count(Orders.OrderID) DESC

如果您需要包括订单中没有商品的客户(不常见但可能)，请将INNER JOIN更改为LEFT OUTER JOIN.

If you need to include Customers that have orders with no items (unusual but possible), change INNER JOIN to LEFT OUTER JOIN.

这篇关于SQL查询，用于统计每个客户的订单数和总金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！