GROUP BY with all combinations of columns-CodePudding

I am trying to figure out how to write a single select that would give me all combinations of values for certain aggregations.

Let me give you an example of data:

COUNTRY  PRODUCT  VALUE
cz       p1       5
cz       p2       10
de       p1       8
de       p2       15

Let's say I would like to get SUM of values grouped by certain criteria for all possible combinations with two column values. The catch is, that ALL values is also an option:

COUNTRY  PRODUCT  SUM(VALUE)
cz       p1       5
cz       p2       10
de       p1       8
de       p2       15
cz       ALL      15
de       ALL      23
ALL      p1       13
ALL      p2       25
ALL      ALL      38

I know I can do this using multiple selects with different groupBy conditions like

SELECT "COUNTRY", "PRODUCT", SUM("VALUE") FROM "PRODUCTS"
SELECT "COUNTRY", "PRODUCT", SUM("VALUE") FROM "PRODUCTS" GROUP BY ("COUNTRY", "PRODUCT")
SELECT "COUNTRY", "PRODUCT", SUM("VALUE") FROM "PRODUCTS" GROUP BY ("COUNTRY")
SELECT "COUNTRY", "PRODUCT", SUM("VALUE") FROM "PRODUCTS" GROUP BY ("PRODUCT")

and combine the results.

But I wonder it it is possible to optimize this and select these combinations with single query. Sorry if this has been answered already, I did not find it.

CodePudding user response：

You are looking for CUBE:

SELECT country, product, SUM(value)
FROM products
GROUP BY CUBE(country, product)
ORDER BY country NULLS LAST, product NULLS LAST;

CodePudding user response：

It is about GROUPING_SETS-clause https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-grouping-sets/