Re: join problem

On sun, 2007-06-24 at 06:14 -0500, A. R. Van Hook wrote:

[ in the future, please avoid top-posting, as it is annoying
to have to rearrange lines when replying ]

> Ragnar wrote:
> > On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote:
> >> Ragnar wrote:
> >>> On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote:
> >>>
> >>>> [problem involving a join, with sum on base table column]

> >>> you are adding the invoice deposit once for each item
> >>>
> >> What is the correct query???
> >
> > sum each invoice separately, and then group the sums by cusid.
> >
> > for example:
> > ...
> > sum(v.deposit)/count(*) as vdeposit
> > from invoice as v
> > left join invoiceitems as i on (v.ivid = i.ivid)
> > group by v.cusid,
> > v.ivid
> > ...

> Works great.
> Can you enlighten me as why the deposit is divided by the number of rows?

maybe an example would clarify a bit:

say you have only one invoice (deposit=100),
with 2 invoiceitems (both with rowtot=50)

"select sum(deposit) from invoice" returns 100
"select sum(rowtot) from invoiceitems" returns 100

the query:
select ivid,deposit,rowtot
from invoice
left join invoiceitems
on (invoice.ivid = invoiceitems.ivid)
returns the rows:
ivid | deposit | rowtot
1 | 100 | 50
1 | 100 | 50

if you just select a SUM(deposit) on that join,
you get 200, not 100 because the value is repeated
for each invoiceitem.

hope this helps

gnari