Re: How to do this query?

> David J Roth <droth_at_adaptron.com> writes:
> David Cui wrote:
> >
> > Dear Netters:
> >
> > Assume I have two tables like these
> >
> > inventory
> > item(char), qty(number)
> >
> > order
> > order_number( char ), item(char), qty(number)
> >
> > I want to have report like this
> >
> > item, sum( qty from inventory ), sum ( qty from order )
> >
> > How can I do this?
> >
> > Thanks
> >
> > David
>
> Try this
>
> select iv.item,sum(iv.qty),sum(od.qty)
> from order od, inventory iv
> where iv.item = od.item
> group by iv.item;
>
> Dave Roth
>

>>>>

The original question did not say what the unique keys, if any to these tables are. If either, or both, of these tables have multiple rows per item, then the sum will be wrong, because each row in iv will be matched with each row in od that has the same item. If they don't have multiple rows per item, then the sum's are not needed.

For example:

iv:
item 1, qty = 10

od:

order=1, item=1, qty=5
order=2, item=1. qty=10

The join without the group by will return:

iv.item=1, iv.qty=10, od.qty=5 - match iv.item=1 with od.order=1 iv.item=1, iv.qty=10, od.qty=10 - match iv.item=1 with od.order=2

With the group by, this will return an inventory qty of 20 instead of 10. It gets worse if there are multiple rows per item in both tables. The solution is to use views that summarise the tables with multiple rows per item and then join the two views to get the desired report.

HTH Paul Received on Sat Aug 10 1996 - 00:00:00 CEST