查询两个不同表中两个字段的 SUM
我正在尝试确定如何在两个表之间求和.
I am trying to determine how to do sum fields between two tables.
在表 1 中，我们将其简称为 gegevens，例如 gegevenID、vertrekdatum、prijs
In table 1, we'll call it gegevens for short, I would have, for example, gegevenID, vertrekdatum, prijs
在表 2 中，我们称之为费用，例如，feeID、gegevenID、金额
In table 2, we'll call it fees, I would have , for example, feeID, gegevenID, amount
我想根据 gegevens 的 year(vertrekdatum) 对 prijs 的值进行求和.
I want to take and sum the values for prijs based on year(vertrekdatum) from gegevens.
我曾尝试进行 LEFT JOIN，但它一直有效，直到费用表中有两条相同 gegevenID 的记录.然后它加倍了 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.
表格示例:
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|
----------------------------
我想要的结果是
TOTALS
--------------------------------------------
| year | SumOfPrijs | SumOfFees | Total |
--------------------------------------------
| 2011 | 1836.14 | 176.00 | 2012.14 |
| 2012 | 2505.10 | 50.00 | 2555.10 |
--------------------------------------------
当考虑到一个 gegevenID 的费用表中有两行时，此查询导致prijs"加倍.
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)
任何想法都会很棒.
推荐答案
在join之前需要使用子查询来聚合fees表:
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);
问题是gegeven"上的多重费用导致连接产生意外的行，从而影响总和.
The problem is that the multiple fees on on "gegeven" is causing the join to produce unexpected rows, that affect the sum.
相关文章