Question 19931 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How can I sum value of all permutations from N to M without repetitions in SQL?

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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:

    t _col1
    d,e,f 87
    a,b,c 84
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search