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)
);  
INSERT INTO packageCredit VALUES
(1,1,'Deposit',25.00),
(2,1,'Balance',92.00);
CREATE TABLE packageItemTax (
 packageItemTaxID INT, 
 packageItemTaxItemID INT, 
 packageItemTaxName CHAR(5), 
 packageItemTaxAmount DECIMAL(10,2)
);
INSERT INTO packageItemTax VALUES
(1,1,'GST',7.00),
(2,1,'HST',10.00);
The query ... 
SELECT 
  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: 
SELECT 
  i.packageItemID AS Item,
  SUM(i.packageItemPrice) AS Price,
  c.Credit,
  t.Tax
FROM packageItem i
JOIN (
  SELECT packageCreditItemID, SUM(packageCreditAmount) AS Credit
  FROM packageCredit
  GROUP BY packageCreditItemID
) c ON i.packageItemID = c.packageCreditItemID
JOIN (
  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.

Last updated 22 Feb 2025