Re: Sum values originated from distinct rows in a join

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 24 Jul 2009 13:51:47 +0200
Message-ID: <4A69A053.5000702_at_gmail.com>

Álvaro G. Vicario schrieb:
> I have this data (it's just a sample for testing purposes):
>
>
> with invoices as (
> select 1000 invoice_id, to_date('2009-07-24', 'YYYY-MM-DD')
> delivered, 70 points from dual union all
> select 2000 invoice_id, to_date('2009-07-24', 'YYYY-MM-DD')
> delivered, 125 points from dual union all
> select 3000 invoice_id, to_date('2009-07-24', 'YYYY-MM-DD')
> delivered, 70 points from dual
> ), lines as (
> select 1000 invoice_id, 29.95 amount from dual union all
> select 1000 invoice_id, 13.25 amount from dual union all
> select 2000 invoice_id, 89.50 amount from dual union all
> select 2000 invoice_id, 33.10 amount from dual union all
> select 2000 invoice_id, 60.25 amount from dual union all
> select 3000 invoice_id, 150.95 amount from dual
> )
> select i.invoice_id, i.delivered, i.points, l.amount
> from invoices i
> inner join lines l on i.invoice_id=l.invoice_id
>
>
> INVOICE_ID DELIVERE POINTS AMOUNT
> ---------- -------- ---------- ----------
> 1000 24/07/09 70 29,95
> 1000 24/07/09 70 13,25
> 2000 24/07/09 125 89,5
> 2000 24/07/09 125 33,1
> 2000 24/07/09 125 60,25
> 3000 24/07/09 70 150,95
>
>
> Now I want to grab a daily summary:
>
>
> [...]
> select i.delivered, count(distinct i.invoice_id) d_invoices,
> sum(i.points) d_points, sum(l.amount) d_amount
> from invoices i
> inner join lines l on i.invoice_id=l.invoice_id
> group by i.delivered
>
>
> DELIVERE D_INVOICES D_POINTS D_AMOUNT
> -------- ---------- ---------- ----------
> 24/07/09 3 585 377
>
>
> But of course D_POINTS should be 265 (70+125+70).
>
> I can get it in a separate query if I get rid on the JOIN:
>
>
> select i.delivered, sum(i.points) d_points
> from invoices i
> group by i.delivered
>
>
> DELIVERE D_POINTS
> -------- ----------
> 24/07/09 265
>
>
> If I want to get the summary in a single query, a subquery apparently
> does the trick:
>
> select i.delivered, count(distinct i.invoice_id) d_invoices, p.d_points
> d_points, sum(l.amount) d_amount
> from invoices i
> inner join lines l on i.invoice_id=l.invoice_id
> inner join (
> select i.delivered, sum(i.points) d_points
> from invoices i
> group by i.delivered
> ) p on i.delivered=p.delivered
> group by i.delivered, p.d_points
>
>
> DELIVERE D_INVOICES D_POINTS D_AMOUNT
> -------- ---------- ---------- ----------
> 24/07/09 3 265 377
>
>
> However, this gets really messy as soon as I add more criteria and
> tables to the query (e.g., daily summary by department, warehouse and
> customer type).
>
> I believe I could get the correct result with SUM() OVER() but I can't
> make it work (I don't understand how analytic clauses work). Is is
> possible?
>
>
>
> (It should work on Oracle Database 10g Enterprise Edition Release
> 10.1.0.2.0. Thanks in advance.)
>

I would probably write it like

[...]
  13 select i.delivered,
  14 count(distinct i.invoice_id) d_invoices,   15 sum(i.points) d_points,
  16 sum(l.amount) d_amount
  17 from invoices i
  18 inner join
  19 (select invoice_id,sum(amount) amount   20 from lines
  21 group by invoice_id) l on i.invoice_id=l.invoice_id   22 group by i.delivered
  23 ;

DELIVERED D_INVOICES D_POINTS D_AMOUNT

------------------- ---------- ---------- ----------
24.07.2009 00:00:00          3        265        377

In general, if one have a master/detail relationship and want aggregate on master table and additionally add some attributes to that aggregate from detail table, the detail table could be preaggregated on the join key to avoid rows duplication from the master table ( it is basically conversion from 1:n relationship to 1:1 relationship)

Best regards

Maxim

-- 
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?

Aksel Peter Jørgensen

Received on Fri Jul 24 2009 - 06:51:47 CDT