Aggregates across multiple joins

from the Artful Common Queries page

Given a parent table and two child tables, a query which sums values in both child tables, grouping on a parent table column, returns sums that are exactly twice as large as they should be. In this example from the MySQL General Discussion list:

DROP TABLE IF EXISTS packageItem,packageCredit,packageItemTax;
CREATE TABLE packageItem (
 packageItemID INT, 
 packageItemName CHAR(20), 
 packageItemPrice DECIMAL(10,2)
INSERT INTO packageItem VALUES(1,'Delta Hotel',100.00);

CREATE TABLE packageCredit (
 packageCreditID INT, 
 packageCreditItemID INT, 
 packageItemType CHAR(10), 
 packageCreditAmount DECIMAL(10,2)

CREATE TABLE packageItemTax (
 packageItemTaxID INT, 
 packageItemTaxItemID INT, 
 packageItemTaxName CHAR(5), 
 packageItemTaxAmount DECIMAL(10,2)

The query ...

  packageItemID             AS Item,
  SUM(packageItemPrice)     AS Price,
  SUM(packageItemTaxAmount) AS Tax,
  SUM(packageCreditAmount)  AS Credit
FROM packageItem
LEFT JOIN packageCredit ON packageItemID=packageCreditItemID
LEFT JOIN packageItemTax ON packageItemTaxItemID=packageItemID
GROUP BY packageItemID
ORDER BY packageItemID;

returns this incorrect result ...

| Item | Price  | Tax   | Credit |
|    1 | 400.00 | 34.00 | 234.00 |

With three child tables, the sums are tripled. Why? The query aggregates across each join. How then to get the correct results? One way is with subqueries in the JOIN:

  i.packageItemID AS Item,
  SUM(i.packageItemPrice) AS Price,
FROM packageItem i
  SELECT packageCreditItemID, SUM(packageCreditAmount) AS Credit
  FROM packageCredit
  GROUP BY packageCreditItemID
) c ON i.packageItemID = c.packageCreditItemID
  SELECT packageItemTaxItemID, SUM(t.packageItemTaxAmount) AS Tax
  FROM packageItemTax t 
  GROUP BY packageItemTaxItemID
) t ON i.packageItemID = t.packageItemTaxItemID
GROUP BY packageItemID;
| Item | Price  | Credit | Tax   |
|    1 | 100.00 | 117.00 | 17.00 |

If subqueries are unavailable or too slow, upgrade your version of MySQL, or if that is not possible then replace the subqueries with temp tables.

Return to the Artful Common Queries page