Saturday, February 25, 2012

Problem with query

I have two tables. One for invoice detail and one for inventory per warehouse. Let's say the one for invoice detail has the following data:

PRODUCT | AMT_SOLD
shirt | 3
pant | 1
shirt | 4
pant | 5

and the inventory per warehouse has the following data:

PRODUCT | WAREHOUSE | QUANTITY
shirt | A1 | 50
shirt | A3 | 30
shirt | A4 | 10
pant | A2 | 25
pant | A3 | 10

I'm trying to make a query that will show me the name of the product, the sum of the quantity sold (from the invoice table) and the sum of the quantity in all of the warehouses, like this:

PRODUCT | SUM1 | SUM2
shirt | 7 | 90
pant | 6 | 35

If I do:
SELECT a.product, SUM(a.amt_sold) as SUM1, SUM(b.quantity) as SUM2
FROM invoice a, inventory b
WHERE a.product = b.product
GROUP BY a.product

I get:

PRODUCT | SUM1 | SUM2
shirt | 21 | 180
pant | 12 | 70

which is obviously not right

any ideas?this..

with a subquery..

select T.product,T.sum1, sum(quantity) as sum2 from
(select product, sum(amt_sold) as sum1 from invoice group by product) as T
inner join inventory as B
on T.product = B.product
group by T.product,T.sum1

try this =)
Ale.

No comments:

Post a Comment