I want to calculate the sum of the charges that will be grouped according to the User and the series of services. And I wonder, how I can joining the "charge" to user table?

I have a user table :

A transaction table:

My expected table is:

I tried standard sum() function:

select u.u_id,u.offer_1,u.offer_2,u.offer_3,sum(t.charge)
from user u
left join transaction t on u.offer_1 = t.offer_id or u.offer_2 = t.offer_id  or u.offer_2 = t.offer_id 
group by u.u_id,u.offer_1,u.offer_2,u.offer_3

Can you help me?

CodePudding user response：

Test data (note I added a row with all null offers in the first table, to test correctness for that case too):

create table user_tbl (u_id, offer_1, offer_2, offer_3) as
    select 101,   11, null, null from dual union all
    select 101, null,   12,   13 from dual union all
    select 101, null,   12, null from dual union all
    select 101, null, null,   13 from dual union all
    select 201,   22,   32,   33 from dual union all
    select 201, null, null, null from dual
;

create table transactions (u_id, offer_id, change) as
    select 101, 11, 150 from dual union all
    select 101, 12, 150 from dual union all
    select 101, 13, 200 from dual union all
    select 201, 22, 300 from dual union all
    select 201, 32, 100 from dual union all
    select 201, 33,  45 from dual
;

Query and output:

with
  prep (rn, u_id, offer_1, offer_2, offer_3, o1, o2, o3) as (
    select rownum, u_id, offer_1, offer_2, offer_3, offer_1, offer_2, offer_3
    from   user_tbl
  )
select u_id, offer_1, offer_2, offer_3, nvl(sum(change), 0) as change
from   prep
unpivot include nulls (offer_id for col in (o1, o2, o3))
left outer join transactions using (u_id, offer_id)
group by u_id, offer_1, offer_2, offer_3, rn
;

      U_ID    OFFER_1    OFFER_2    OFFER_3     CHANGE
---------- ---------- ---------- ---------- ----------
       101         11                              150
       101                    12                   150
       101                    12         13        350
       101                               13        200
       201         22         32         33        445
       201                                           0

Unpivoting brings the data to a more familiar form, with one row per combination of u_id and "offer". The join is an outer join for two reasons: both because the unpivoting may generate null offers, and because a non-null offer may not have a match in the transactions table. (For that matter, a u_id may not have a match there either.) The rest is what you would expect - the outer join and the aggregation - and using nvl in the main select, assuming the change should be shown as zero (rather than null) when a row shows no actual offers.