How to join with a group by and where, including 0 values-CodePudding

I've got some sort of static table like:

SELECT * FROM targets;

name   | target
name1  |      6
name2  |      3
name3  |      2

and another table with the actual records inside, that I want to measure:

SELECT * FROM records;

id   | name  | created_on   
546  | name1 | 2022-06-02  
547  | name2 | 2022-06-04  
548  | name2 | 2022-06-07

Now I'd like to build a table, that groups / counts the records per name (per month). The group by is easy (except the 0 values):

SELECT "name", COUNT("id") FROM "records"
WHERE MONTH("created_on") = MONTH(CURRENT_DATE())
AND YEAR("created_on") = YEAR(CURRENT_DATE())
GROUP BY "name"

But how do I join both tables to get a result like this?

name   | target | current
name1  |      6 |       1
name2  |      3 |       2
name3  |      3 |       0

CodePudding user response：

your data

CREATE TABLE targets(
   name   VARCHAR(70) NOT NULL 
  ,target INTEGER  NOT NULL
);
INSERT INTO targets
(name,target) VALUES 
('name1',6),
('name2',3),
('name3',2);

CREATE TABLE records(
   id         INTEGER  NOT NULL 
  ,name       VARCHAR(70) NOT NULL
  ,created_on DATE  NOT NULL
);
INSERT INTO records
(id,name,created_on) VALUES 
(546,'name1','2022-06-02'),
(547,'name2','2022-06-04'),
(548,'name2','2022-06-07');

you should use Join(Left or Right) to include all values including null values and IF logical operator and Subquery to act as table for your query

SELECT targets.name,
       target,
       IF(current IS NULL, 0, current) AS current
FROM   targets 
       LEFT JOIN (SELECT name,
                         Count(id) current
                  FROM   records
                  WHERE  Month(created_on) = Month(CURRENT_DATE())
                         AND Year(created_on) = Year(CURRENT_DATE())
                  GROUP  BY name) records
              ON records.name = targets.name

dbfiddle