How to join tables with many-to-many relationship and group by month in SQL

Viewed 55

I have two tables: one with Salespeople monthly targets and the other with deals they closed.

enter image description here

enter image description here

I need to consolidate the amount closed for each salesperson and month, and join with the first table. Find the expected result below.

enter image description here

I've tried the query below but it won't join the months if no deal was closed.

SELECT
         S."Salesperson",
         month(S."Date"),
         year(S."Date"),
         S."Date",
         S."Target",
         sum(D."Amount") AS 'Amount',
FROM  "SalesQuotes" AS  S
LEFT JOIN "Deals" AS  D ON D."Salesperson"  = S."Salesperson"
     AND    (year(D."Date")  = year(S."Date")
     AND    month(D."Date")  = month(S."Date"))  
GROUP BY S."Salesperson",
     month(S."Date"),
     year(S."Date"),
     S."Target",
      S."Date" 

MREs:

First table

CREATE TABLE SalesQuotes(
   Date        VARCHAR(6) NOT NULL
  ,Salesperson VARCHAR(6) NOT NULL
  ,Target      INTEGER  NOT NULL
);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','John',12);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','John',14);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','John',16);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','Mary',20);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','Mary',25);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','Mary',30);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','George',50);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','George',60);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','George',70);

Second table

CREATE TABLE Deals(
   Date        VARCHAR(6) NOT NULL
  ,Salesperson VARCHAR(6) NOT NULL
  ,Amount      INTEGER  NOT NULL
  ,Deal        VARCHAR(1) NOT NULL
);
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('jan/21','John',5,'A');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('jan/21','John',6,'B');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('feb/21','Mary',10,'C');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('feb/21','George',15,'D');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('mar/21','George',40,'E');
0 Answers
Related