The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Summing parts of a column

yaronkoralyaronkoral Community Edition User

I have a column which stores multiple vertical bar separated values.
For example:
40|30|20
70
100|20|1|1|1|10
I would like to get a sum of all parts of the string:
90
70
133
Is there a way to do it in Vertica?

Answers

  • SruthiASruthiA Vertica Employee Employee

    @yaronkoral : Please find the solution below. You need to use another column from your table to sum up the results of a column of a particular row

    dbadmin=> select * from test_f;
    a | b
    -----------------+---
    40|30|20 | 1
    70 | 2
    100|20|1|1|1|10 | 3
    (3 rows)

    dbadmin=> select b, sum(a) from (SELECT b,v_txtindex.stringTokenizerDelim(a, '|') OVER(partition by b order by b) as a FROM test_f order by 1,2) g group by 1;
    b | sum
    ---+-----
    1 | 90
    2 | 70
    3 | 133
    (3 rows)

    dbadmin=>

  • yaronkoralyaronkoral Community Edition User

    Thank you @SruthiA, works perfectly!

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2021

    @yaronkoral - Just for fun, here is another route via a User Defined SQL Function:

    verticademos=> SELECT * FROM test_f;
            a
    -----------------
     40|30|20
     70
     100|20|1|1|1|10
    (3 rows)
    
    verticademos=> CREATE OR REPLACE FUNCTION sumvalue (x VARCHAR) RETURN INT
    verticademos-> AS
    verticademos-> BEGIN
    verticademos->   RETURN
    verticademos->     DECODE(split_part(x, '|', 1), '', '0', split_part(x, '|', 1)) +
    verticademos->     DECODE(split_part(x, '|', 2), '', '0', split_part(x, '|', 2)) +
    verticademos->     DECODE(split_part(x, '|', 3), '', '0', split_part(x, '|', 3)) +
    verticademos->     DECODE(split_part(x, '|', 4), '', '0', split_part(x, '|', 4)) +
    verticademos->     DECODE(split_part(x, '|', 5), '', '0', split_part(x, '|', 5)) +
    verticademos->     DECODE(split_part(x, '|', 6), '', '0', split_part(x, '|', 6)) +
    verticademos->     DECODE(split_part(x, '|', 7), '', '0', split_part(x, '|', 7)) +
    verticademos->     DECODE(split_part(x, '|', 8), '', '0', split_part(x, '|', 8)) +
    verticademos->     DECODE(split_part(x, '|', 9), '', '0', split_part(x, '|', 9)) +
    verticademos->     DECODE(split_part(x, '|', 10), '', '0', split_part(x, '|', 10));
    verticademos-> END;
    CREATE FUNCTION
    
    verticademos=> SELECT a, sumvalue(a) sum FROM test_f;
            a        | sum
    -----------------+-----
     40|30|20        |  90
     70              |  70
     100|20|1|1|1|10 | 133
    (3 rows)
    

    If you have to handle more that 10 values to sum, you'd have to add more SPLIT_PART calls :smiley:

  • marcothesanemarcothesane - Select Field - Administrator

    Or yet another two:
    a) convert to array and explode:

    -- need an identifier to link back to the row ...                                                                                                           
    WITH
    indata(id,lists) AS (
                SELECT 1, '40|30|20'
      UNION ALL SELECT 2, '100|20|1|1|1|10'
    )
    ,
    pivoted AS (
      SELECT
        EXPLODE(
          id
        , lists
        , STRING_TO_ARRAY(
            '['||REPLACE(lists,'|',',')||']'
          )
        ) OVER(PARTITION BEST)
        AS (id,lists,idx,summand)
      FROM indata
    )
    -- test query ...
    -- SELECT * FROM pivoted;
    -- out  id |      lists      | idx | summand 
    -- out ----+-----------------+-----+---------
    -- out   2 | 100|20|1|1|1|10 |   0 | 100
    -- out   2 | 100|20|1|1|1|10 |   1 | 20
    -- out   2 | 100|20|1|1|1|10 |   2 | 1
    -- out   2 | 100|20|1|1|1|10 |   3 | 1
    -- out   2 | 100|20|1|1|1|10 |   4 | 1
    -- out   2 | 100|20|1|1|1|10 |   5 | 10
    -- out   1 | 40|30|20        |   0 | 40
    -- out   1 | 40|30|20        |   1 | 30
    -- out   1 | 40|30|20        |   2 | 20
    SELECT
      id
    , lists
    , SUM(summand) As the_sum_per_row
    FROM pivoted
    GROUP BY 1,2;
    -- out  id |      lists      | the_sum_per_row 
    -- out ----+-----------------+-----------------
    -- out   2 | 100|20|1|1|1|10 |             133
    -- out   1 | 40|30|20        |              90
    

    b) CROSS JOIN with an in-line index table ...

    WITH                                                                                                                                                        
    indata(id,lists) AS (
                SELECT 1, '40|30|20'
      UNION ALL SELECT 2, '100|20|1|1|1|10'
    )
    ,
    -- need an index of integers,here ...
    i(i) AS (
                SELECT 0
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
    )
    ,
    pivoted AS (
      SELECT
        id
      , lists
      , i AS idx
      , SPLIT_PART(lists,'|',i+1)::INT AS summand
      FROM indata CROSS JOIN i
      WHERE SPLIT_PART(lists,'|',i+1) <>''
      ORDER BY id,idx
    )
    -- control query ...
    -- SELECT * FROM pivoted;
    -- out  id |      lists      | idx | summand 
    -- out ----+-----------------+-----+---------
    -- out   1 | 40|30|20        |   0 |      40
    -- out   1 | 40|30|20        |   1 |      30
    -- out   1 | 40|30|20        |   2 |      20
    -- out   2 | 100|20|1|1|1|10 |   0 |     100
    -- out   2 | 100|20|1|1|1|10 |   1 |      20
    -- out   2 | 100|20|1|1|1|10 |   2 |       1
    -- out   2 | 100|20|1|1|1|10 |   3 |       1
    -- out   2 | 100|20|1|1|1|10 |   4 |       1
    -- out   2 | 100|20|1|1|1|10 |   5 |      10
    SELECT
      id
    , lists
    , SUM(summand) As the_sum_per_row
    FROM pivoted
    GROUP BY 1,2;
    -- out  id |      lists      | the_sum_per_row 
    -- out ----+-----------------+-----------------
    -- out   1 | 40|30|20        |              90
    -- out   2 | 100|20|1|1|1|10 |             133
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file