Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: 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?
    Last edited by diegocro; 09-03-04 at 03:07.

  2. #2
    Join Date
    Aug 2003
    Posts
    23

    you can use..

    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.
    Alx_81 =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •