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

Álvaro G. Vicario schrieb:
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

On Jul 24, 4:19*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

See http://www.orafaq.com/usenet/comp.da...08/03/0224.htm

jg
--
@home.com is bogus.
But you don't have to worry until God tweets back:
http://www3.signonsandiego.com/stori...god/?uniontrib