SQLize Online / PHPize Online  /  SQLtest Online

-- create CREATE TABLE Accruals ( Id INTEGER PRIMARY KEY, AccountId INTEGER, Period date, InBalance decimal, Calculation decimal ); CREATE TABLE Payments ( Id INTEGER PRIMARY KEY, AccountId INTEGER, Date date, Sum decimal ); -- insert insert into Accruals values (1, 808251, '03/01/2020', 4000, 2000); insert into Accruals values (2, 808251, '04/01/2020', 3000, 3000); insert into Accruals values (3, 808251, '05/01/2021', 2000, 5000); insert into Payments values (1, 808251, '03/15/2020', 1000); insert into Payments values (2, 808251, '04/15/2020', 2000); insert into Payments values (3, 808251, '05/15/2021', 3000); insert into Payments values (4, 808251, '06/15/2021', 1000); -- fetch SELECT a.Key, COALESCE(Sum(a.InBalance), 0) as Total_InBalance, COALESCE(Sum(a.Calculation), 0) as Total_Calculation, COALESCE(Sum(p.Sum), 0) as Total_Sum FROM (SELECT ((date_part('year', a.Period)::int * 12 + date_part('month', a.Period)::int) - 1) / 12 AS Key, Sum(a.InBalance) as InBalance, Sum(a.Calculation) as Calculation FROM Accruals AS a GROUP BY ((date_part('year', a.Period)::int * 12 + date_part('month', a.Period)::int) - 1) / 12) as a FULL OUTER JOIN (SELECT ((date_part('year', p.Date)::int * 12 + date_part('month', p.Date)::int) - 1) / 12 AS Key, Sum(p.Sum) as Sum FROM Payments AS p GROUP BY ((date_part('year', p.Date)::int * 12 + date_part('month', p.Date)::int) - 1) / 12) as p ON a.Key = p.Key ORDER BY a.Key DESC