Mysql - Aggregates across multiple join mysql joins, Mysql Aggregates table

Aggregates across multiple join mysql joins

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 correlated subqueries:

packageItemID AS Item,
SUM(packageItemPrice) AS Price,
( SELECT SUM(c.packageCreditAmount)
FROM packageCredit c
WHERE c.packageCreditItemID = packageItemID
) AS Credit,
( SELECT SUM(t.packageItemTaxAmount)
FROM packageItemTax t
WHERE t.packageItemTaxItemID = packageItemID
) AS Tax
FROM packageItem
GROUP BY packageItemID;

| Item | Price  | Credit | Tax   |
| 1    | 100.00 | 117.00 | 17.00 |
Moving the subquery logic to the JOIN level may speed up performance considerably:

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

The topic on Mysql - Aggregates across multiple join mysql joins is posted by - Malu

Hope you have enjoyed, Mysql - Aggregates across multiple join mysql joinsThanks for your time

Tech Bluff