0

So I got a View of items in Postgres that looks like this:

id brand_id category_id color_id material_id pattern_id style_id
1 100 101 100 101 null 101
1 100 101 101 102 null 102
2 101 101 102 103 102 null
2 101 101 102 103 103 null
3 105 101 null 103 104 null
4 101 101 102 103 103 105

I need all DISTINCT possible combinations (excluding item_id) but forming groups that not always will include all columns.

Example of combinations for the First Row (but I need the same for all items without getting duplicated groups):

brand_id category_id color_id material_id pattern_id style_id
100 null null null null null
null 101 null null null null
null null 100 null null null
null null null 101 null null
null null null null null 101
100 101 null null null null
100 null 100 null null null
100 null null 101 null null
100 null null 101 null null
100 null null 101 null 101
100 101 100 null null null
100 101 null 101 null null
100 101 null null null 101

I tried a CROSS JOIN with the original categories tables but I get a enormous table of combinations and also I don't get the groups of 1, 2, 3, 4 or 5 categories, I get the combinations including ALL six categories

Improve this question
2
  • What is the logic for not including a group of (100,101,100,101,...,...)? Commented Aug 23, 2022 at 0:35
  • I need to get statistics for multiple combinations for example: get all the ownership of brand_id = 100 and color_id = 100 only. Or all purchases from pattern_id = 100 AND material_id = 101 (The combination are endless) Commented Aug 23, 2022 at 0:42

1 Answer 1

Reset to default
3

You can use CUBE subclause for GROUP BY, which produces the following output for your first row:

    select 
100 brand_id,   101 category_id,    100 color_id,   
101 material_id,    null pattern_id,    101 style_id
    group by
    cube (brand_id, category_id,    color_id,   material_id ,pattern_id ,style_id);
brand_id category_id color_id material_id pattern_id style_id
100 100 101 101
100 101 101
101 101
101
100 100
100 101 100 101
100 100 101
100 100 101
101 100 101
101 100 101
101 101 101
101 100 101 101
100 101 100
100 101 100 101
101
100 101
100 100 101
100 101
100 101 101
100 100 101 101
101 101
101 100 101
101 100 101 101
101
101 101
100 101 100 101 101
100
100 101
100 101 100 101
101
101 101
101 100 101
100 101
100 101
100 101 101
101 101
101 101
101 101 101
100 101 101 101
101 100
101 100
100 101 100
100
100 101
100 101 101
100
100 101
100 101
100 101 101
101
100 101
100 101 101
100 101 101
101
100 101
100 101 101
100 101 101 101
100 101 100 101 101
100
100 100
100 100 101
100 101 100 101

View on DB Fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Exactly what I needed! Didn't know about the CUBE subclause, very useful! Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.