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 FROM (SELECT a.Key, Sum(a.InBalance), Sum(a.Calculation) FROM Accruals AS a GROUP BY ((date_part('year', a.Period)::int * 12 + date_part('month', a.Period)::int) - 1) / 12 AS Key) FULL OUTER JOIN (SELECT p.Key, Sum(p.Sum) FROM Payments AS p GROUP BY ((date_part('year', p.Date)::int * 12 + date_part('month', p.Date)::int) - 1) / 12 AS Key) ON a.Key = p.Key ORDER BY a.Key DESC;