Query SUM for two fields in two different tables

问题:

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.

回答1:

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.

回答2:

Joining in any form will double the values when there are two gegevenID rows in fees (or triple them if there are three, and so on).

The best workaround I can think of is to calculate the sums independently - one subquery for price and one subquery for fees - and then put the results together:

SELECT
  p.year,
  p.SumOfPrijs,
  f.SumOfFees,
  p.SumOfPrijs + f.SumOfFees AS Total
FROM (
  SELECT
    YEAR(vertrekdatum) AS year,
    SUM(prijs) AS SumOfPrijs
  FROM gegevens
  GROUP BY YEAR(vertrekdatum)
) p
LEFT JOIN (
  SELECT
    YEAR(vertrekdatum) as year,
    SUM(amount) AS SumOfFees
  FROM gegevens
  INNER JOIN fees ON gegevens.gegevenID = fees.gegevenID
  GROUP BY YEAR(vertrekdatum)
) f ON p.year = f.year

There's a SQL Fiddle here.