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

https://www.postgresql.org/docs/10/queries-table-expressions.html#QUERIES-GROUPING-SETS
['Natarajan Santhosh']
['Postgres', 'Grouping And Sorting']