Reputation: 11
With this query:
With EX1 AS
(
SELECT
idcompany,
idemploy,
import
FROM data
WHERE
idcompany in ('000405', '000102')
)
SELECT *
FROM EX1
UNION ALL
SELECT idcompany, 'Total', sum(import) from EX1
GROUP BY idcompany
ORDER BY
idcompany,
idemploy
I get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
And I'd like to get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
Grand Total 420
What code do I have to add to the query, and where should I add it? Thanks.
Upvotes: 1
Views: 66
Reputation: 31746
You may use the ROLLUP
sub-clause of the GROUP BY
clause (Postgres 9.5+) along with the Grouping
function which is ideally suited for such operations and is efficient than those UNION ALL
s
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Upvotes: 0
Reputation: 6184
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
Upvotes: 2