SQL 对两个表中的值求和和分组

Question

我正在尝试根据卖家名称对销售数据进行分组。该名称在另一个表中可用。我的表格如下所示:

发票行:

+-----------+----------+-----+----------+
| InvoiceNr | Title    | Row |  Amount  |
+-----------+----------+-----+----------+
|  1        | Chair    |  1  |  2000.00 |
|  2        | Sofa     |  1  |  1500.00 |
|  2        | Cushion  |  2  |  2000.00 |
|  3        | Lamp     |  1  |  6500.00 |
|  4        | Table    |  1  |  -500.00 |
+-----------+----------+-----+----------+

发票头:

+-----------+----------+------------+
| InvoiceNr | Seller   | Date       |
+-----------+----------+------------+
|  1        | Adam     | 2016-01-01 |
|  2        | Lisa     | 2016-01-04 |
|  3        | Adam     | 2016-01-08 |
|  4        | Carl     | 2016-01-17 |
+-----------+----------+------------+

我目前正在使用的查询如下所示:

SELECT SUM(Amount)
FROM InvoiceRow
WHERE InvoiceNr IN (
  SELECT InvoiceNr
  FROM InvoiceHead
  WHERE Date >= '2016-01-01' AND Date < '2016-02-01'
)

这行得通，并将对 1 月份所有发票(总销售额)的所有行的值求和。

我想要做的是按每个卖家的名字分组的销售摘要。像这样:

+----------+------------+
| Seller   | Amount     |
+----------+------------+
| Adam     | 8500.00    |
| Lisa     | 3500.00    |
| Carl     | -500.00    |
+----------+------------+

在那之后甚至可能按月分组(但这不是这个问题的一部分，我希望如果我解决了这个问题能够弄清楚)。

我尝试了各种联接，但最终得到了很多重复项，而且我不确定如何同时求和和分组。有谁知道如何做到这一点？

Answer 1

试试这个

SELECT seller, SUM(amount) FROM InvoiceRow 
JOIN InvoiceHead 
ON InvoiceRow.InvoiceNr = InvoiceHead.InvoiceNr
GROUP BY InvoiceHead.seller;

或者如果你想在两个日期之间。试试这个

SELECT seller, SUM(amount) FROM InvoiceRow 
JOIN InvoiceHead 
ON InvoiceRow.InvoiceNr = InvoiceHead.InvoiceNr
WHERE InvoiceHead.Date >= '2016-01-01' AND InvoiceHead.Date < '2016-02-01'
GROUP BY InvoiceHead.seller;