Search code examples
sqlpostgresqlgreenplum

Take sum of a concatenated column SQL


I want to use post and pre revenue of an interaction to calculate net revenue. Sometimes there are multiple customers in an interaction. The data is like:

InteractionID | Customer ID |   Pre  |  Post
--------------+-------------+--------+--------
      1       | ab12        |   10   |  30
      2       | cd12        |   40   |  15
      3       | de12;gh12   | 15;30  | 20;10

Expected output is to take sum in pre and post call to calculate net

    InteractionID | Customer ID   |   Pre  |  Post | Net 
    --------------+---------------+--------+-------+------
          1       |  ab12         |   10   |  30   | 20
          2       |  cd12         |   40   |  15   | -25
          3       |  de12;gh12    |   45   |  30   | -15

How do I get the net revenue column?


Solution

  • The proper solution is to normalize your relational design by adding a separate table for customers and their respective pre and post.

    While stuck with the current design, this would do it:

    SELECT *, post - pre AS net
    FROM (
       SELECT interaction_id, customer_id 
             ,(SELECT sum(x::numeric) FROM string_to_table(pre, ';') x) AS pre
             ,(SELECT sum(x::numeric) FROM string_to_table(post, ';') x) AS post
       FROM   tbl
       ) sub;
    

    db<>fiddle here

    string_to_table() requires at least Postgres 14.
    You did not declare your Postgres version, so I assume the current version Postgres 14.

    For older versions replace with regexp_split_to_table() or unnest(string_to array)).