Group two tables by month and make difference

Question

I try to regroup data (with SQL) by months and make a subtraction (with groups by months).

TABLE 1 :

DATE	MONEY1.
01/01/2023	30
10/01/2023	5
12/02/2023	30
18/02/2023	20

TABLE 2 :

DATE	MONEY2.
04/01/2023	5
8/01/2023	10
19/02/2023	5
20/02/2023	5

And I want to get this as my result:

MONTH	MONEY1.	MONEY2.	MONEY1-MONEY2
January	35	15	20
February	50	10	40

Thank you for your help !

I can group by month each table (in separate SELECT commands), but I can't make two operations in same table.

Answer 1

You can use group by and appropriate function to extract month from your date (in the example oracle dialect is being used) to sum money in your tables. And the join those aggregated results:

with t1 as(
    select sum(MONEY1) sum1, TRUNC(date_, 'MM') mon from table1
    group by TRUNC(date_, 'MM')
),
t2 as (
    select sum(MONEY2) sum2, TRUNC(date_, 'MM') mon from table2
    group by TRUNC(date_, 'MM')
)
select coalesce(t1.mon,t2.mon), sum1, sum2, sum1-sum2
from t1
full outer join t2
on (t1.mon=t2.mon)

Demo can be seen here.

For BASE query would look something like this:

select coalesce(t1.yea,t2.yea), coalesce(t1.mon,t2.mon), sum1, sum2, sum1-sum2
from (
    select sum(MONEY1) sum1, YEAR(date) yea, MONTH(date) mon 
    from table1
    group by YEAR(date), MONTH(date)) t1
full outer join (
    select sum(MONEY2) sum2, YEAR(date) yea, MONTH(date) mon
    from table2
    group by YEAR(date), MONTH(date)) t2
on (t1.mon=t2.mon and t1.yea=t2.yea)