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 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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值