How to get correct summaries with analytics? <p>I want to get summary numbers from the <code>cust_detail</code> table if a specific <code>invoice_code</code> appears in <code>invoice_detail</code>. </p> <p>In this example, I'd like to report cust_detail summaries only for batches <code>10</code> and <code>20</code> because they are the ones with <code>invoice_code='9999'</code>. But the duplication in the <code>invoice_detail</code> table is skewing my numbers.</p> <pre><code>with invoice_detail as ( select '10' as invoice_batch, '9999' as invoice_code from dual union all select '10' as invoice_batch, '9999' as invoice_code from dual union all select '20' as invoice_batch, '1111' as invoice_code from dual union all select '30' as invoice_batch, '9999' as invoice_code from dual ), cust_detail as ( select '1' as cust_id, '10' as invoice_batch, 40 as points_paid, 30 as points_earned, 30 as points_delivered from dual union all select '1' as cust_id, '20' as invoice_batch, 10 as points_paid, 10 as points_earned, 10 as points_delivered from dual union all select '1' as cust_id, '30' as invoice_batch, 20 as points_paid, 15 as points_earned, 5 as points_delivered from dual ) select cust_id, sum(points_paid) over (partition by c.invoice_batch order by cust_id) batch_total from cust_detail c inner join invoice_detail i on c.invoice_batch=i.invoice_batch where i.invoice_code = '9999'; </code></pre> <p>Desired results:</p> <pre><code> CUST_ID PAID EARNED DELIVERED TOT_PAID TOT_EARNED TOT_DELIVERED --------- ------ -------- ----------- ---------- ------------ --------------- 1 40 30 30 60 45 40 1 20 15 5 60 45 40 </code></pre>