How to add a 'total' row in a grouped query (in Postgresql)?

Question

How do I add a row to the end of this SELECT so I can see the total of the grouped rows? (I need the total for 'money' and 'requests':

SELECT 
    organizations.name || ' - ' || section.name as Section, 
    SUM(requests.money) as money, 
    COUNT(*) as requests
FROM 
    schema.organizations
   -- INNER JOINs omitted --
WHERE 
    -- omitted --
GROUP BY 
    -- omitted --
ORDER BY 
    -- omitted --

Running the above produces:

|*Section*  | *Money* | *Requests*|
|-----------|---------|-----------|
|BMO - HR   |564      |10         |
|BMO - ITB  |14707    |407        |
|BMO - test |15       |7          |

Now what I want is to add a total to the end of that which would display:

|BMO - Total|15286    |424        |

---

I have tried a few things, and ended up by trying to wrap the select in a WITH statement and failing:

WITH w as (
    --SELECT statement from above--
)
SELECT * FROM w UNION ALL 
   SELECT 'Total', money, requests from w

This produces weird results (I'm getting four total rows - when there should be just one.

Any help would be much appreciated!

Thanks

Answer 1

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

Answer 2

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