MySQL查询-对一个相关表求和-对另一个相关表进行计数 [英] MySQL Query - Sum one related table - Count another related table

问题描述

该Count与一个相关表配合良好，但是当我添加Sum时，该Count中的结果就显得过高了.

The Count works fine with one related table, but when I add the Sum I get overinflated results in the Count.

SELECT Orders.kp_OrderID AS `Order #`, 
    Orders.t_ServiceLevel AS `Service Level`, 
    Customers.t_CustCompany AS Company, 
    Orders.t_JobName AS `Job Name`, 
    Orders.t_JobStatus AS `Status`, 
    Orders.d_JobDue AS Due, 
    Orders.ti_JobDue AS Time, 
    COUNT(OrderItems.kf_OrderID) AS `Line Item`, 
    SUM(
        `OrderItemComponents`.`n_QuantityPrintMaterial` * (
        `OrderItemComponents`.`n_WidthInInches` * `OrderItemComponents`.`n_HeightInInches` / 144
            )
        ) AS SqFt, 
    Orders.nb_JobFinished AS Finished
FROM Orders 
     INNER JOIN OrderItems ON Orders.kp_OrderID = OrderItems.kf_OrderID
     INNER JOIN Customers ON Orders.kf_CustomerID = Customers.kp_CustomerID
     INNER JOIN OrderItemComponents ON OrderItemComponents.kf_OrderID = Orders.kp_OrderID
    WHERE Orders.d_JobDue > (
        DATE_SUB(CURDATE(), INTERVAL 60 DAY)
         )
    GROUP BY Orders.kp_OrderID
    ORDER BY `Line Item` DESC

推荐答案

将您的COUNT行切换为:

Switch your COUNT line to:

COUNT(DISTINCT OrderItems.kf_OrderID) AS `Line Item`

添加行时

INNER JOIN OrderItemComponents 
  ON OrderItemComponents.kf_OrderID = Orders.kp_OrderID

您返回了更多的行，因为我假设OrderItem可以包含多个组件.

you returned many more rows since an OrderItem can, I assume, contain multiple components.

更新

如果您有COUNT条语句，请尝试就地使用它:

Try using this in place if your COUNT statement:

(SELECT COUNT(OrderItems.kf_OrderID) 
 FROM OrderItems.kf_OrderID 
 WHERE OrderItems.kf_OrderID=Order.kf_OrderID) AS `Line Item`,

这篇关于MySQL查询-对一个相关表求和-对另一个相关表进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！