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

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

2 Answers

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 

The rollup function in this answer might be a convenient way to do this. More on this and related functions here:

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