Query SUM for two fields in two different tables

mysql, sum

Solution

You need to use a subquery to aggregate the fees table before the join:

SELECT sum(prijs) as SumOfPrijs, sum(amount) as SumOfFees, sum(prijs)+sum(amount) AS   
Total, year(vertrekdatum) as year
FROM tbl_vluchtgegevens vg LEFT JOIN
     (select f.gegevenId, sum(amount) as Amount
      from tbl_fees f
      group by f.gegevenId
     ) f
     ON f.gegevenID = vg.gegevenID
WHERE vertrekdatum <=NOW()
GROUP by year(vertrekdatum);

The problem is that the multiple fees on on "gegeven" is causing the join to produce unexpected rows, that affect the sum.

Problem

I am trying to determine how to do sum fields between two tables. In table 1, we'll call it gegevens for short, I would have, for example, gegevenID, vertrekdatum, prijs In table 2, we'll call it fees, I would have , for example, feeID, gegevenID, amount I want to take and sum the values for prijs based on year(vertrekdatum) from gegevens. I had tried to do a LEFT JOIN and it worked until there were two records for the same gegevenID in the fee's table., then it doubled the prijs. Table example: ``` GEGEVENS ---------------------------------- gegevenID | vertrekdatum | prijs | ---------------------------------- | 1 | 2011-01-01 |1385.88| | 2 | 2011-03-01 | 450.26| | 3 | 2012-01-01 |2505.10| ---------------------------------- FEES ---------------------------- feeID | gegevenID | amount | ---------------------------- | 1 | 2 | 50.00| | 2 | 2 | 126.00| | 3 | 3 | 50.00| ---------------------------- ``` The results that I want are ``` TOTALS -------------------------------------------- | year | SumOfPrijs | SumOfFees | Total | -------------------------------------------- | 2011 | 1836.14 | 176.00 | 2012.14 | | 2012 | 2505.10 | 50.00 | 2555.10 | -------------------------------------------- ``` This query resulted in the doubled 'prijs' when it took into account there were two rows in the fees table for one gegevenID. ``` SELECT sum(prijs) as SumOfPrijs, sum(amount) as SumOfFees, sum(prijs)+sum(amount) AS Total, year(vertrekdatum) as year FROM tbl_vluchtgegevens vg LEFT JOIN tbl_fees f ON f.gegevenID = vg.gegevenID WHERE vertrekdatum <=NOW() GROUP by year(vertrekdatum) ``` Any thoughts would be great.

Original source