mysql某个表求和语句_MySQL查询-对一个相关表求和-对另一个相关表进行计数

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

解决方案

Switch your COUNT line to:

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

When adding the line

INNER JOIN OrderItemComponents

ON OrderItemComponents.kf_OrderID = Orders.kp_OrderID

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

UPDATE

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