From: Peter Brawley
Date: April 18 2006 2:51pm
Subject: Re: My Left Joins are Doubling the SUM()
List-Archive: http://lists.mysql.com/mysql/196992
Message-Id: <4444FD08.5090200@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

mysql,
> My Left Joins are Doubling the SUM()
> SELECT packageItemID, packageItemName,packageItemPrice
> ,SUM(packageItemTaxAmount) as packageItemTaxAmount
> ,SUM(packageCreditAmount) as packageCreditAmount
> FROM packageItem
> LEFT JOIN packageCredit ON packageItemID=packageCreditItemID
> LEFT JOIN packageItemTax ON packageItemTaxItemID=packageItemID AND 
> packageItemTaxActive=1
> GROUP BY packageItemID
> ORDER BY packageItemID
That why God invented correlated subqueries :-). Try something like ...

SELECT
  packageItemID,
  SUM(packageItemPrice),
  ( SELECT SUM(c.packageCreditAmount)
    FROM packageCredit c
    WHERE c.packageCreditItemID = packageItemID
  ) AS CreditSum,
  ( SELECT SUM(t.packageItemTaxAmount)
    FROM packageItemTax t
    WHERE t.packageItemTaxItemID = packageItemID
  ) AS TaxSum
FROM packageItem

PB

-----

> packageItem Table
> packageItemID | packageItemName | packageItmePrice
> 1 | Delta Hotel | 100.00
> packageCredit Table
> packageCreditID | packageCreditItemID | packageItemType | 
> packageCreditAmount
> 1 | 1 | Deposit | 25.00
> 2 | 1 | Balance | 92.00
> packageItemTax
> packageItemTaxID | packageItemTaxItemID | packageItemTaxName | 
> packageItemTaxAmount
> 1 | 1 | GST | 7.00
> 2 | 1 | HST | 10.00
> The desired result of the query should be:
> 1 | Delta Hotel | 100.00 | 17.00 | 117.00
> But it keeps doubling the tax and the credit amounts and results look 
> like this
> 1 | Delta Hotel | 100.00 | 34.00 | 234.00
> is there a way to execute this query without this happening?
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.3/317 - Release Date: 4/18/2006