Sum two columns from two tables without cartesian

Question

I searched for a while but nothing I find fixes my problem...

I have two tables: earnings, expenses

I have the columns:

earnings.amount
earnings.date
expenses.amount
expenses.date

I want to sum the amount of all my earnings and show them by year and month, so I do this and it works perfectly...

SELECT
     DATE_FORMAT(date, '%Y%m') AS 'date',
     SUM(amount) as 'total_earnings'
FROM earnings
GROUP BY DATE_FORMAT(date, '%Y%m')

The output is similar to this...

date,total_earnings
201901,5000
201902,5500
201903,6200
etc.

I do the same for the expenses table and all is well.

My problem: When I try to write a query that shows me total earnings and total expenses grouped by year and month I get a cartesian result that shows huuuge numbers.

This is an example query that shows a cartesian result:

SELECT
    DATE_FORMAT(ea.date, '%Y%m') AS date,
    SUM(ea.amount) AS 'total_earnings',
    SUM(ex.amount) AS 'total_expenses',
FROM earnings ea
    INNER JOIN expenses ex
        ON DATE_FORMAT(ea.date, '%Y%m') = DATE_FORMAT(ex.date, '%Y%m')
GROUP BY DATE_FORMAT(ex.date, '%Y%m')

How can I show the total amount of monthly earnings and monthly expenses in a single query?

Desired output:

date,total_earnings,total_expenses
201901,5000,1000
201902,5500,1100
201903,6200,1250

Thanks in advance!

You should do the join AFTER the sum has been made, ie, two subqueries to get date, sum() and then join them using the date field. — JuanN, Mar 24 '20 at 16:31

Eric · Accepted Answer · 2020-03-24T17:16:08.620

1

You need to aggregate first, then JOIN

SELECT ea.date, total_earnings, total_expenses
FROM (
    SELECT DATE_FORMAT(date, '%Y%m') AS date
        , SUM(amount) AS total_earnings
    FROM earnings 
    GROUP BY DATE_FORMAT(date, '%Y%m')
) ea
JOIN (
    SELECT DATE_FORMAT(date, '%Y%m') AS date
        , SUM(amount) AS total_expenses
    FROM expenses
    GROUP BY DATE_FORMAT(date, '%Y%m')
) ex ON ex.date = ea.date

edited Mar 24 '20 at 17:16

answered Mar 24 '20 at 16:50

Eric

3,165
1
19
25

Thanks, Eric. I'm trying to translate this logic to my real table and column names... your code has an additional comma after each 'AS date'. – Haddock-san Mar 24 '20 at 16:57
@Haddock-san Updated. – Eric Mar 24 '20 at 17:16
This way the logic that worked, thanks! I had to tweak it a lot for it to fit exactly into what I needed (the example in the question was over-simplified). Thanks, Eric! – Haddock-san Mar 24 '20 at 18:01
What about when there's only earnings or expense in one month, the `join` will not include that in the results. – Kerkouch Mar 24 '20 at 18:11
@Kerkouch Then he can turn it into `LEFT/RIGHT/FULL JOIN` as required. – Eric Mar 24 '20 at 18:13
Left join & right join won't work, also I guess MySQL does not support FULL JOIN. – Kerkouch Mar 24 '20 at 18:21
@Kerkouch Why won't `LEFT/RIGHT JOIN` not work? There are many reference how to do `FULL JOIN` in `MySQL`. – Eric Mar 24 '20 at 19:12

P.Salmon · Answer 2 · 2020-03-24T16:54:26.943

Perhaps you don't need a join ..

drop table if exists t;
create table t
(expense_amount int,expense_date date, earnings_amount int, earnings_date date);

insert into t values
(10,'2020-01-01',null,null),
(10,'2020-01-01',null,null),
(10,'2020-02-01',null,null),
(null,null,10,'2020-01-01'),
(10,'2020-03-01',10,'2020-03-01');


select coalesce(expense_date,earnings_date) date,
         sum(case when expense_amount is not null then expense_amount else 0 end) expense,
         sum(case when earnings_amount is not null then earnings_amount else 0 end) earnings
from t
group by coalesce(expense_date,earnings_date);

+------------+---------+----------+
| date       | expense | earnings |
+------------+---------+----------+
| 2020-01-01 |      20 |       10 |
| 2020-02-01 |      10 |        0 |
| 2020-03-01 |      10 |       10 |
+------------+---------+----------+
3 rows in set (0.001 sec)

If your data model is different in that 1 row can contain expense and earnings with different dates then this won't work for you and you should generate the totals separately and join them.

Kerkouch · Answer 3 · 2020-03-24T17:38:09.550

You can achieve that using a subquery:

SELECT `date`, SUM(earnings) AS total_earnings, SUM(expenses) AS total_expenses
FROM (
         SELECT DATE_FORMAT(earnings.date, '%Y%m') AS `date`, `amount` AS earnings, 0 AS expenses
         FROM earnings
         UNION
         SELECT DATE_FORMAT(expenses.date, '%Y%m') AS `date`, 0 AS earnings, `amount` AS expenses
         FROM expenses
     ) expenses_earnings
GROUP BY `date`

Edit:

Using YEAR(date) and MONTH(date) instead of DATE_FORMAT, that will allow you to calculate the total of each year and the grand total using rollup.

SELECT `year`, `month`, SUM(earnings) AS total_earnings, SUM(expenses) AS total_expenses
FROM (
         SELECT YEAR(earnings.date) AS `year`, MONTH(earnings.date) AS `month`, `amount` AS earnings, 0 AS expenses
         FROM earnings
         UNION
         SELECT YEAR(expenses.date) AS `year`, MONTH(expenses.date) AS `month`, 0 AS earnings, `amount` AS expenses
         FROM expenses
     ) expenses_earnings
GROUP BY `year`, `month`

Sum two columns from two tables without cartesian

3 Answers3

Edit: