SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to find the differences between the values of a specific column based on a key?

    I've two tables as purchase_details and invoice_details

    and i want to store the inventory/stock of each product from the data of these two tables.

    structure of purchase_details.

    Code:
    'pid', 'int(10)'
    'product_id', 'int(10)' 
    'quantity', 'float(8,2)'
    'amount', 'float(12,2)'
    'expiry_date', 'date'
    structure of invoice_details.

    Code:
    'invoice_id', 'int(10) unsigned'
    'product_id', 'int(10) unsigned'
    'quantity', 'float(10,2)'
    'price', 'float(12,2)'
    i want to calculate the total quantity of remaining stock for every product i.e
    (quantity of sum of products from purchase_details) - (quantity of sum of products from invoice_details)

    The invoice_details may or may not have entries for any product_id, so if there is no data available for a particular product_id in invoice_details then the value present in the purchase_details should be listed.

    Product_id is would be same for the two tables.

    Product table consists of product data, and the structure is

    Code:
    'id', 'int(10) unsigned'
    'cid', 'int(10) unsigned'
    'name', 'varchar(90)'
    'selling_price', 'float(10,2)'
    'mrp', 'float(10,2)'
    'reorder_level', 'bigint(20) unsigned'
    the invoice_details may or may not have entries for every product_id.

    how can i proceed?

    I tried the following query but it returns wrong answer,.
    Code MySQL:
    select a.pid, a.amount - b.amount from 
    (select product_id as pid, COALESCE(sum(quantity),0) amount 
    from purchase_details group by product_id) a, 
    (select product_id as pid, COALESCE(sum(quantity), 0) amount 
    from invoice_details group by product_id) b 
    group by a.pid;
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Replace the last Group clause with a where clause to tie the two tables you created together,

    Code SQL:
    SELECT a.pid, a.amount - b.amount
     FROM (SELECT product_id AS pid, COALESCE(SUM(quantity),0) amount 
              FROM purchase_details GROUP BY product_id) a, 
              (SELECT product_id AS pid, COALESCE(SUM(quantity), 0) amount 
               FROM invoice_details GROUP BY product_id) b 
    WHERE a.product_id = b.product_id;

  3. #3
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats working fine but it returns only the columns where the id's of two tables match.

    For example , i have two product_ids 7,8 in purchase_details table and have entries in invoice_details for only the product_id 7..

    So it returns the stock for only product_id 7. i want to get stock for every product..
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT a.pid, a.amount - b.amount
     FROM (SELECT product_id AS pid, COALESCE(SUM(quantity),0) amount 
              FROM purchase_details GROUP BY product_id) a
    LEFT JOIN
              (SELECT product_id AS pid, COALESCE(SUM(quantity), 0) amount 
               FROM invoice_details GROUP BY product_id) b 
              ON a.product_id = b.product_id;

    If you do a left join between the first and second tables that b.amount will come back as NULL. I believe (but not certain) that it will be treated as zero in you expression.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also change your float columns to decimal. float is subject to rounding errors.


Bookmarks

Posting Permissions

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