Question 114659 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – GROUP BY with all combinations of columns

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.

Tags:

3

Answers


  2. 0

    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;
    Login or Signup to reply.
  3. 0

    If your data is hierarchical (you want to analyze the data by country, or all countries combined, but not the total of each products), prefer the "GROUP BY ROLLUP" over the "GROUP BY CUBE" clause.

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

| country | product | SUM |
|---------|---------|-----|
| cz      | p1      | 5   |
| cz      | p2      | 10  |
| cz      | NULL    | 15  |
| de      | p1      | 8   |
| de      | p2      | 15  |
| de      | NULL    | 23  |
| NULL    | p1      | 13  |
| NULL    | p2      | 25  |
| NULL    | NULL    | 38  |

    or

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

| country | product | SUM |
|---------|---------|-----|
| cz      | p1      | 5   |
| cz      | p2      | 10  |
| cz      | NULL    | 15  |
| de      | p1      | 8   |
| de      | p2      | 15  |
| de      | NULL    | 23  |
| NULL    | NULL    | 38  |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search