Hi!

I have two tables ('sell' and 'buy') that contains the products I have sold
or bought:

TABLE SELL:
   PRODUCT   QUANTITY
       0001                2

TABLE BUY:
   PRODUCT    QUANTITY
       0001                1

I need to show the SUM of sold units and the SUM of bought units of each
product, but I always get wrong values.


This is what I try:

   select SUM(sell.quantity) as sold, SUM(buy.quantity) as bought from
sell, buy where sell.PRODUCT = buy.PRODUCT


And this is what I get:

RESULT:
   sold    bought
      2        2  ??

I have tried to group by product, delete the 'where' clause, etc... but the
result is always the same.
How can I get the real sum of each table?

"Santy Concepción" <santyweb@hotmail.com> wrote in message
news:03CC380B-4F3F-4D16-A801-8856BBDB19D4@news.elevatesoft.com...
> Hi!
>
> I have two tables ('sell' and 'buy') that contains the products I have
> sold or bought:
>
> TABLE SELL:
>    PRODUCT   QUANTITY
>        0001                2
>
> TABLE BUY:
>    PRODUCT    QUANTITY
>        0001                1
>
> I need to show the SUM of sold units and the SUM of bought units of each
> product, but I always get wrong values.
>
>
> This is what I try:
>
>    select SUM(sell.quantity) as sold, SUM(buy.quantity) as bought from
> sell, buy where sell.PRODUCT = buy.PRODUCT
>
>
> And this is what I get:
>
> RESULT:
>    sold    bought
>       2        2  ??
>
> I have tried to group by product, delete the 'where' clause, etc... but
> the result is always the same.
> How can I get the real sum of each table?
>

It is a bit complicated, especially if you need to account for unmatched
items on either side (a product with sales but no purchases, or viceversa).

I would combine the two tables into one before doing the aggregation, as
follows:

SELECT PROD PROD, QTY PURCHASES, 0 SALES
INTO MEMORY\TEMP
FROM BUY
UNION ALL
SELECT PROD, 0, QTY
FROM SELL;
SELECT PROD, SUM(PURCHASES) PURCHASES, SUM(SALES) SALES
FROM MEMORY\TEMP
GROUP BY PROD;

Make sure you use UNION ALL, otherwise you will lose identical purchases or
sales for the same product.

Robert