I need to write a query that sums the values of all possible permutations and shows only one row for the result. I also need to mention that table is stored as an array and that number of items in the column table can be bigger or smaller than 3, for example, x,y, y,x or o,p,q,r, p,q,r,o and so on.

I tried with a hardcoded query but seems like there are too much of different possibilities that can show up.

    SELECT 
    case 
       when replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'a,b'
       or replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'b,a' then 'a,b'
       when replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'c,d'
       or replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'd,c' then 'c,d'
    END AS tables_list,
    SUM(value) AS value
    FROM my_table
    GROUP BY 1

I have a table that looks like this:

    table value
    a,b,c   10    
    b,c,a   21
    c,b,a   12
    a,c,b   13
    b,a,c   16
    c,a,b   12
    d,e,f   15
    e,f,d   12
    f,e,d   13
    d,f,e   16
    e,d,f   11
    f,d,e   20
    ...

What I would like to get is to sum all the possible permutations and store it under one table, i.e. for a,b,c (10 21 12 13 16 12) = 84:

    table value
    a,b,c   84
    d,e,f   87

CodePudding user response：

If I understand your question correctly, I have part of an answer. Observe that all permutations are identical after sorting. If you have a function that sorts a string, such that

string_sort('c,b,a')

produces

a,b,c

then the SQL writes itself:

select T, sum(value)
from (select string_sort(table) as T, value
      from tables_list ) as A
group by T

I can't be more specific, because I don't know the feature set for your DBMS.

CodePudding user response：

You can use array and string functions to order strings and group:

-- sample data
WITH dataset("table", value) AS (
 values ('a,b,c', 10),
    ('b,c,a', 21),
    ('c,b,a', 12),
    ('a,c,b', 13),
    ('b,a,c', 16),
    ('c,a,b', 12),
    ('d,e,f', 15),
    ('e,f,d', 12),
    ('f,e,d', 13),
    ('d,f,e', 16),
    ('e,d,f', 11),
    ('f,d,e', 20)
),

-- query parts
ordered as (
    select array_join(
            array_sort(
                split("table", ',')), 
                ','
        ) t
        , value
    from dataset
)

select t, sum(value)
from ordered
group by t;

Output: