CODEWITHSUNDEEP
sqlpostgresql
Lucia Deetz
Lucia Deetz

Reputation: 11

How to add grand total to this query?

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

Answers (2)

Kaushik Nayak
Kaushik Nayak

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 ALLs

Docs

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);

Demo

Upvotes: 0

Selaron
Selaron

Reputation: 6184

You can append a

UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1

to your query.

Upvotes: 2

Related Questions