How to add a 'total' row in a grouped query (in Postgresql)?
The rollup function in this https://stackoverflow.com/a/54913166/1666637 answer might be a convenient way to do this. More on this and related functions here: https://www.postgresql.org/docs/devel/queries-table-expressions.html#QUERIES-GROUPING-SETS
Something like this, untested code
WITH w as (
--SELECT statement from above--
)
SELECT * FROM w ROLLUP((money,requests))
Note the double parentheses, they are significant
You can achieve this by using a UNION query. In the query below, I add an artificial sortorder column and wrap the union query in an outer query so that the sum line appears at the bottom.
[I'm assuming you'll be adding your joins and group by clauses...]
SELECT section, money, requests FROM -- outer select, to get the sorting right.
( SELECT
organizations.name || ' - ' || section.name as Section,
SUM(requests.money) as money,
COUNT(*) as requests,
0 AS sortorder -- added a sortorder column
FROM
schema.organizations
INNER JOINs omitted --
WHERE
-- omitted --
GROUP BY
-- omitted --
-- ORDER BY is not used here
UNION
SELECT
'BMO - Total' as section,
SUM(requests.money) as money,
COUNT(*) as requests,
1 AS sortorder
FROM
schema.organizations
-- add inner joins and where clauses as before
) AS unionquery
ORDER BY sortorder -- could also add other columns to sort here