Get monthly sums from several tables with same (date,amount) columns I have records stored in &quot;date,amount&quot; 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 &quot;Multiple FULL OUTER JOIN on multiple tables&quot;, that uses an &quot;anchor&quot; 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.
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&lt;&gt;fiddle here