Sum values originated from distinct rows in a join

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Fri, 24 Jul 2009 13:19:14 +0200
Message-ID: <h4c5bj$kgg$1_at_news.eternal-september.org>

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

-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Received on Fri Jul 24 2009 - 06:19:14 CDT