Take sum of a concatenated column SQL-CodePudding

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?

CodePudding user response：

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)).