CODEWITHSUNDEEP
sqloracle-databaseoracle11g
zundarz
zundarz

Reputation: 1594

How to get summary based on another table group summary?

Business Rules: Get total cust_points for customer only if total(trans_amount) for a trans_code grouping > 0.

For customer #1, the summary at date_code level (code 10) is > 0 so cust_points total = 70.

For customer #2 only code 20 group totals > 0 so total only 75 total cust_points

Here's my query: 

 with customers as
 (select '1' as cust_id, 10 as date_code, 30 as cust_points from dual union all
  select '1' as cust_id, 10 as date_code, 40 as cust_points from dual union all
  select '1' as cust_id, 20 as date_code, 22 as cust_points from dual union all --These points should not total because trans_amount sum for code 20  is  less than 0
  select '1' as cust_id, 40 as date_code, 33 as cust_points from dual union all  -- These points should not total because there is not trans_amounts > 0 for date_code
  select '2' as cust_id, 10 as date_code, 20 as cust_points from dual union all
  select '2' as cust_id, 20 as date_code, 65 as cust_points from dual union all
  select '2' as cust_id, 20 as date_code, 10 as cust_points from dual 
  ),
 transactions_row as
 (
 select '1' as cust_id, '10' as trans_code, 10.00   as trans_amount from dual union all
 select '1' as cust_id, '20' as trans_code, -15.00   as trans_amount from dual union all
 select '1' as cust_id, '20' as trans_code, -20.00   as trans_amount from dual union all
 select '1' as cust_id, '20' as trans_code, -10.00  as trans_amount from dual union all
 select '1' as cust_id, '30' as trans_code, 30.00   as trans_amount from dual union all
 select '1' as cust_id, '20' as trans_code, -20.00   as trans_amount from dual union all
 select '2' as cust_id, '10' as trans_code, -50.00   as trans_amount from dual union all
 select '2' as cust_id, '20' as trans_code, 20.00   as trans_amount from dual
 )
 select cust_id,
        sum(cust_points)
 from customers 
 where cust_id in
( 
select cust_id 
       from (
 select cust_id, trans_code, sum(trans_amount) 
 from transactions_row
 group by cust_id, trans_code
 having sum(trans_amount) > 0 
 )
 )
 group by cust_id



Desired Results

 CUST_ID    CUST_POINTS 
  1         70  /* (30 because total trans_amount for tran_code(10) > 0  +
                    40 because total trans_amount for tran_code(10) > 0) */

   2        75  /* Do not include the 20 points because total trans_amt for 10 < 0 */

Upvotes: 0

Views: 37

Answers (1)

sgeddes
sgeddes

Reputation: 62861

Here's one way using exists:

 select cust_id,
        sum(cust_points)
 from customers c
 where exists (
     select 1
     from transactions_row tr 
     where tr.trans_code = c.date_code
       and tr.cust_id = c.cust_id
     group by tr.trans_code, tr.cust_id
     having sum(tr.trans_amount) > 0
 )
 group by cust_id

Upvotes: 1

Related Questions