Hi

My guess is you have multiple records in xLedgItem for some / all entries in xItemUsages. Thus the much larger values with the join.

I would change it to ....

select sum(iu.QtySold * iu.ItemPrice),
sum(iu.QtyUsed * iu.ItemPrice),
(Select Sum(SumField) FROM xLedgItem li Where li.InvItemID = iu.InvItemID)
from xItemUsages iu
where iu.MinorID = 900033



Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com
Wild Software Ltd

----- Original Message -----
From: sgharp
To: firebird-support@yahoogroups.com
Sent: Thursday, December 09, 2004 10:25 AM
Subject: [firebird-support] Strange Query Results



Hi All,

I'm using FB v1.5 and IBExpert v2004.08.05. I've found a situation
where the query result set seems really wierd.

select sum(iu.QtySold * iu.ItemPrice), sum(iu.QtyUsed * iu.ItemPrice)
from xItemUsages iu
where iu.MinorID = 900033

yields results of $2,538.6535 and $2,718.5150.

However,

select sum(iu.QtySold * iu.ItemPrice), sum(iu.QtyUsed * iu.ItemPrice)
from xItemUsages iu
join xLedgItem li on li.InvItemID = iu.InvItemID
where iu.MinorID = 900033

yields results of $19,769.4608 and $21,230.3890.

My original intent was to add the sum of a field in the xLedgItem
table but, when I added the join to that table, my result set when
crazy. As you can see, I have no ambiguous field references and all
the fields in both result sets are exactly the same. The only
difference is the join line.

Where is this result coming from?

Thanks for any help in understanding this,
Steve




[Non-text portions of this message have been removed]