SQL sum and group values from two tables

Question

I'm trying to group sales data based on a sellers' name. The name is available in another table. My tables look like this:

InvoiceRow:

+-----------+----------+-----+----------+
| 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 |
+-----------+----------+-----+----------+

InvoiceHead:

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

The query that I'm working with currently looks like this:

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

This works and will sum the values of all rows of all invoices (total sales) in the month of january.

What I want to do is a sales summary grouped by each sellers' name. Something like this:

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

And after that maybe even grouped by month (but that's not part of this question, I'm hoping to be able to figured that out if I solve this).

I've tried all kinds of joins but I end up with a lot of duplicates, and I'm not sure how to SUM and group at the same time. Does anyone know how to do this?

Answer 1

Try This

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

OR If you want to between two date. Try This

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;

Answer 2

You just need to join the tables, filter result by date as you need and then make grouping:

select
   H.Seller,
   sum(R.Amount) as Amount
from InvoiceHead as H
    left outer join InvoiceRow as R on R.InvoiceNr = H.InvoiceNr
where H. Date >= '2016-01-01' AND H.Date < '2016-02-01' 
group by H.Seller