Funding Stats View is incorrect.

[elliotcourant]

SELECT bank_account.bank_account_id,
       bank_account.account_id,
       funding_schedule.funding_schedule_id,
       count(expenses.spending_id)                                 AS number_of_expenses,
       count(goals.spending_id)                                    AS number_of_goals,
       sum(COALESCE(expenses.next_contribution_amount, 0::bigint)) AS next_expense_contribution,
       sum(COALESCE(goals.next_contribution_amount, 0::bigint))    AS next_goal_contribution
FROM bank_accounts bank_account
         JOIN funding_schedules funding_schedule ON funding_schedule.account_id = bank_account.account_id AND
                                                    funding_schedule.bank_account_id = bank_account.bank_account_id
         LEFT JOIN spending expenses ON expenses.account_id = funding_schedule.account_id AND
                                        expenses.bank_account_id = funding_schedule.bank_account_id AND
                                        expenses.funding_schedule_id = funding_schedule.funding_schedule_id AND
                                        expenses.spending_type = 0
         LEFT JOIN spending goals ON goals.account_id = funding_schedule.account_id AND
                                     goals.bank_account_id = funding_schedule.bank_account_id AND
                                     goals.funding_schedule_id = funding_schedule.funding_schedule_id AND
                                     goals.spending_type = 1
GROUP BY bank_account.bank_account_id, bank_account.account_id, funding_schedule.funding_schedule_id;

bank_account_id	account_id	funding_schedule_id	number_of_expenses	number_of_goals	next_expense_contribution	next_goal_contribution
4	1	3	136	136	226832	943364
2	1	1	0	1	0	0
1	1	2	2	2	113500	37566

The totals for the view are all wrong. The joins need to be converted into subqueries.