Re: [SQL] join problem

[A. R. Van Hook]

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

thanks

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:
  

If I try an inclusive query using the following:
 select
  sum(i.rowtot + i.tax) as tot,
  sum(v.deposit) as deposit
from cai c
join invoice   v on (v.cusid = c.cusid)
left join invoiceitems i on (v.ivid = i.ivid)
where v.cusid = 2128
group by 
  c.cusid

I get
tot| deposit
--+-
 1179.240 | 2819.24


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:

select vcusid as cusid,
   sum(vtot) as tot,
   sum(vdeposit) as deposit
from (
   select
  v.cusid as vcusid,
  v.ivid as vivid,
  sum(i.rowtot + i.tax) as vtot,
  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
 ) as vsums
where vsums.vcusid=2128
group by vsums.vcusid


hope this helps
gnari



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org
  


--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana

[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 564-0769 - Cell



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] join problem

[Ragnar]

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



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq