0

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.

1
  • Just a word of advice - you should not be using SQL keywords as table or column names - it makes debugging more difficult, portability more difficulty, is generally confusing and bad practice to boot - month and `date, are two such keywords!
    – Vérace
    Jan 10 at 21:42
0

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

3
  • @nbl: Thank you. Your solution seems working for me, but I will have to check this more in detail.
    – OuzoPower
    Jan 10 at 19:54
  • 1
    Use UNION ALL instead of single UNION - this will improve the performance.
    – Akina
    Jan 11 at 4:48
  • I have added UNION ALL
    – nbk
    Jan 11 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.