如何对不同的表进行计数和求和(SQL)

Question

我正试图编写一个SQL语句来实现这一点：

对于每个有订单的客户，我需要列出客户的编号、客户的订单数量、这些订单上的项目总数以及这些项目的总价。然后我需要根据客户编号对结果进行排序。

我有下面的代码。它有效，但结果不正确。对于1个客户，它说订单数量是2，但只有1个订单，价格就像是2个订单一样。另一位客户有3个订单，但显示为4个。我想我错过了一个联接函数？

我有3个表，一个客户表(Customer_Name(，订单表(order_num，order_date，Customer_num(，项目表(item_num，order_num，quantity，total_price(

SELECT   customer.customer_num AS "Customer Number",
COUNT(DISTINCT orders.order_num) AS "Order Amount",
SUM(items.quantity) AS "Quantity of Items",
SUM(items.total_price) AS "Total Price w/o shipping"
FROM     items, orders, customer
WHERE    customer.customer_num = orders.customer_num
AND      orders.order_num = items.order_num
GROUP BY customer.customer_num
ORDER BY customer.customer_num ASC;

任何帮助都会很棒。谢谢

Answer 1

从逻辑上讲，您的查询看起来是正确的，但在FROM子句中使用逗号编写得很糟糕。此外，您不需要加入customers表。

因此，我建议：

SELECT o.customer_num AS "Customer Number",
COUNT(DISTINCT o.order_num) AS "Order Amount",
SUM(i.quantity) AS "Quantity of Items",
SUM(i.total_price) AS "Total Price w/o shipping"
FROM orders o JOIN
items i
ON o.order_num = i.order_num
GROUP BY o.customer_num
ORDER BY o.customer_num ASC;

COUNT(DISTINCT)应该在计算订单方面做你想做的事情。