MySQL Query - Sum one related table - Count another related table

Question

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

I'm trying to return Orders w/ a count of related rows from OrderItems while at the same time summarizing related rows from OrderItemsComponents. I get the correct results until I add the Sum and the last Inner Join for OrderItemComponents — user1551971, Jul 25 '12 at 15:28

Holger Brandt · Accepted Answer · 2012-07-25T15:31:38.197

2

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

edited Jul 25 '12 at 15:31

answered Jul 25 '12 at 14:54

Holger Brandt

4,324
1
20
35

I'm trying to return the order w/ the count of related rows for OrderItems. W/ this solution I'm getting "1" as the answer for the # of related rows in OrderItems for each Order. – user1551971 Jul 25 '12 at 15:25
@user1551971, I amended my answer to use a separate subselect. – Holger Brandt Jul 25 '12 at 15:32
Holger - I think its close, but know I'm returning the total count of all rows related to the subset. Obviously I'm no expert. 1285 rows returned and 45697 OrderItems returned for each row. Thanks! – user1551971 Jul 25 '12 at 15:53

score 1 · Answer 2 · answered Jul 25 '12 at 14:55

1

I assume that you get each order item repeated for every order item component. One solution might be COUNT(DISTINCT OrderItems.kf_OrderID) so you don't count the same item twice.

answered Jul 25 '12 at 14:55

MvG

57,380
22
148
276

MySQL Query - Sum one related table - Count another related table

2 Answers2

Linked