Summing parts of a column

Answers

• SruthiA Administrator

  June 2021

  @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=>

  2
• yaronkoral Community Edition User ✭

  June 2021

  Thank you @SruthiA, works perfectly!

  0
• Jim_Knicely - Select Field - Administrator

  June 2021 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

  0
• marcothesane - Select Field - Administrator

  June 2021

  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
  

  0