查询两个不同表中两个字段的SUM [英] Query SUM for two fields in two different tables

问题描述

我正在尝试确定如何对两个表之间的字段求和.

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)

任何想法都会很棒.

推荐答案

在加入之前，您需要使用子查询来汇总费用表:

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.

这篇关于查询两个不同表中两个字段的SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！