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.
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