SQLize | PHPize | SQLtest

CREATE TABLE subjects ( id INT PRIMARY KEY ); INSERT INTO subjects VALUES (1), (2), (3), (4), (5), (6); CREATE TABLE expenses ( id INT, sub_id int references subjects(id), spent float ); INSERT INTO expenses (id, sub_id, spent) VALUES (1, 1, 1); INSERT INTO expenses (id, sub_id, spent) VALUES (2, 2, 2); INSERT INTO expenses (id, sub_id, spent) VALUES (3, 3, 3); INSERT INTO expenses (id, sub_id, spent) VALUES (4, 2, 1); INSERT INTO expenses (id, sub_id, spent) VALUES (5, 3, 1); CREATE TABLE income1 ( id INT, sub_id int references subjects(id), income float ); INSERT INTO income1 (id, sub_id, income) VALUES (1, 1, 3); INSERT INTO income1 (id, sub_id, income) VALUES (2, 2, 4); INSERT INTO income1 (id, sub_id, income) VALUES (3, 4, 5); INSERT INTO income1 (id, sub_id, income) VALUES (4, 4, 5); INSERT INTO income1 (id, sub_id, income) VALUES (5, 5, 1); CREATE TABLE income2 ( id INT, sub_id int references subjects(id), income float ); INSERT INTO income2 (id, sub_id, income) VALUES (1, 3, 5); INSERT INTO income2 (id, sub_id, income) VALUES (2, 5, 3); INSERT INTO income2 (id, sub_id, income) VALUES (3, 6, 7); select subjects.id, coalesce(income1.income, 0) + coalesce(income2.income, 0) income, coalesce(expenses.spent, 0) spent from subjects left join ( select sub_id, sum(spent) spent from expenses group by sub_id) expenses on expenses.sub_id = subjects.id left join ( select sub_id, sum(income) income from income1 group by sub_id) income1 on income1.sub_id = subjects.id left join ( select sub_id, sum(income) income from income2 group by sub_id) income2 on income2.sub_id = subjects.id order by subjects.id;