查询两个不同表中两个字段的SUM

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

在表1中,我们将其简称为gegevens,例如,gegevenID,vertrekdatum,prijs

In table 2, we'll call it fees, I would have , for example, feeID, gegevenID, amount

在表2中,我们将其称为费用,例如,我会有费用ID,gegevenID,金额

I want to take and sum the values for prijs based on year(vertrekdatum) from gegevens.

我想根据gegevens中的年份(vertrekdatum)对prijs的值进行求和。

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.

我曾尝试进行LEFT JOIN,直到费用表中有两个相同gegevenID的记录,然后它将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.

当考虑到费用表中有两行gegevenID时,此查询导致加倍'prijs'。

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.

任何想法都会很棒。

2 个解决方案

#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.

问题是“gegeven”上的多项费用导致联接产生意外的行,这会影响总和。

#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).

当费用中有两个gegevenID行时,以任何形式加入都会使值加倍(如果有三个,则加倍三次,依此类推)。

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.

这里有一个SQL小提琴。

#1