PostgreSQL GROUPING SETS

How to use grouping sets in postgres

I prefer running postgres via docker

# create these directory on your home docker/volumes/postgres & docker/volumes/temp
$ docker run --rm   --name pg-docker -e POSTGRES_PASSWORD=docker -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data -v $HOME/docker/volumes/temp:/datastore  postgres
$ docker exec -it pg-docker bash
$ root@d7a21c8e5498:/# psql -h localhost -U postgres -d postgres
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

Create a table and use grouping sets.

postgres=# CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);
 
INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic', 200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic', 300);
postgres=# select GROUPING(brand)::boolean isbrandGrouped, GROUPING(segment)::boolean isSegmentGrouped, brand,segment, sum(quantity) from sales group by GROUPING SETS ((brand,segment), (brand), (segment), ())
;

 isbrandgrouped | issegmentgrouped | brand | segment | sum
----------------+------------------+-------+---------+-----
 t              | t                |       |         | 700
 f              | f                | XYZ   | Basic   | 300
 f              | f                | ABC   | Premium | 100
 f              | f                | ABC   | Basic   | 200
 f              | f                | XYZ   | Premium | 100
 f              | t                | ABC   |         | 300
 f              | t                | XYZ   |         | 400
 t              | f                |       | Basic   | 500
 t              | f                |       | Premium | 200
(9 rows)

References

PostgreSQL GROUPING SETS

https://www.postgresql.org/docs/10/queries-table-expressions.html#QUERIES-GROUPING-SETS