Get monthly sums from several tables with same (date,amount) columns

Question

I have records stored in "date,amount" columns from several tables: table1, table2, table3, table4. Amongst tables, field names are same.

Table 1:

date	amount
2018-02-17	10.00
2018-06-25	18.00
2018-07-09	10.00
2018-07-21	5.00

For each month available in any of the table, I want to sum the amounts coming from all four tables, keeping one column per table:

month	amount_table1	amount_table2	amount_table3	amount_table4
2018-02	10.00	null	null	12.00
2018-03	null	null	3.00	8.00
2018-04	null	16.00	3.00	null
2018-05	null	null	3.00	12.00
2018-06	18.00	1.00	null	null
2018-07	15.00	2.00	7.00	1.00

The following code is highly inspired from Roman Pekar's answer to the question "Multiple FULL OUTER JOIN on multiple tables", that uses an "anchor" table. I appreciate this solution as it is easy to read.

I attempted extending it to the case where records are stored for distinct dates and were not already summed by month. It is almost working, but for some reason the monthly sums in the output array are wrong.

SELECT
    X.month,
    SUM(A.montant) AS 'amount_table1',
    SUM(B.montant) AS 'amount_table2',
    SUM(C.montant) AS 'amount_table3',
    SUM(D.montant) AS 'amount_table4'
FROM (
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table1`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table2`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table3`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table4`
) AS X
    LEFT OUTER JOIN table1 AS A ON DATE_FORMAT(A.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table2 AS B ON DATE_FORMAT(B.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table3 AS C ON DATE_FORMAT(C.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table4 AS D ON DATE_FORMAT(D.date_comptabilisation,'%Y-%m') = X.month
WHERE 1
GROUP BY  X.month

I also attempted adding a stricted WHERE clause, with no more success.

WHERE
    DATE_FORMAT(A.date,'%Y-%m') = X.month
 OR DATE_FORMAT(B.date,'%Y-%m') = X.month
 OR DATE_FORMAT(C.date,'%Y-%m') = X.month
 OR DATE_FORMAT(D.date,'%Y-%m') = X.month

Can someone explain what's wrong with my code? Thanks.

Answer 1

It is hard to explain where you go wrong, because your logic doesn't fit in mysql. AS you can see in my sample, you have to add a column that describes, from which table you get the data

This you can use to SUM the amount

CREATE TABLE table1
    (`date` varchar(10), `amount` int)
;
    
INSERT INTO table1
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table2
    (`date` varchar(10), `amount` int)
;
    
INSERT INTO table2
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table3
    (`date` varchar(10), `amount` int)
;
    
INSERT INTO table3
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table4
    (`date` varchar(10), `amount` int)
;
    
INSERT INTO table4
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

SELECT 
CONCAT(YEAR(`date`),'-',MONTH(`date`)) AS yearmonth,
SUM(IF(groupby = 'table1' , `amount`,0)) AS 'amount_table1'
,SUM(IF(groupby = 'table2' , `amount`,0)) AS 'amount_table2'
,SUM(IF(groupby = 'table3' , `amount`,0)) AS 'amount_table3'
,SUM(IF(groupby = 'table4' , `amount`,0)) AS 'amount_table4'
FROM
(SELECT `date`, `amount`,'table1'  AS groupby FROM table1
UNION ALL
SELECT `date`, `amount`,'table2' AS groupby FROM table2
UNION ALL
SELECT `date`, `amount`,'table3' AS groupby FROM table3
UNION ALL
SELECT `date`, `amount`,'table4' AS groupby FROM table4) t1
GROUP BY CONCAT(YEAR(`date`),'-',MONTH(`date`))

yearmonth | amount_table1 | amount_table2 | amount_table3 | amount_table4
:-------- | ------------: | ------------: | ------------: | ------------:
2018-2    |            10 |            10 |            10 |            10
2018-6    |            18 |            18 |            18 |            18
2018-7    |            15 |            15 |            15 |            15

db<>fiddle here