Announcement

Announcement Module
Collapse
No announcement yet.

a problem with calculation

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • a problem with calculation

    hi all

    I have one question . I my DB I have 3 tables
    1)Products(id, name)
    2)Incoming_products(id, quantity, measurement, product_id)
    3)Outgoing_products(id, quantity, measurement, product_id)

    now I need to calculate the difference between incoming and outgoing products.
    I have a procedure but it doesn't work correctly ( (

    BEGIN
    select incoming_products.product_id, sum(incoming_products.measurement)- sum(outgoing_products.measurement) as diff
    from incoming_products, outgoing_products, products
    where incoming_products.product_id=outgoing_products.pro duct_id and incoming_products.product_id=products.id GROUP by incoming_products.product_id;
    END

    this procedure returns wrong values
    if someone could help me, please reply this topic.

  • #2
    -

    Comment


    • #3
      shahaneh wrote on Fri, 25 June 2010 05:27



      BEGIN select incoming_products.product_id, sum(incoming_products.measurement)- sum(outgoing_products.measurement) as diff from incoming_products, outgoing_products, products where incoming_products.product_id=outgoing_products.pro duct_id and incoming_products.product_id=products.id GROUP by incoming_products.product_id; END


      this procedure returns wrong values
      if someone could help me, please reply this topic.



      Firstly, try to ask a meaningful question instead of just saying "but it doesn't work correctly" and "returns wrong values"

      But Im guessing you should rewrite your query using *explicit* JOIN syntax.
      Maybe its not apparent, but listing comma seperated tables and putting your criteria in the WHERE clause, is synonymous with an INNER JOIN.

      Maybe what you actually want is a LEFT JOIN, so that products which have an incoming record, but don't have an outgoing, are not filtered out of the result set....

      Similar to this...


      select incoming_products.product_id, sum(incoming_products.measurement)- sum(IFNULL(outgoing_products.measurement, 0)) as difffrom products INNER JOIN incoming_products ON (incoming_products.product_id=products.id) LEFT JOIN outgoing_products ON (incoming_products.product_id=outgoing_products.pr oduct_id)GROUP by incoming_products.product_id;

      Comment


      • #4
        I think you need 2 outer joins. Just because you sell something doesn't mean you received any (at least not during the same time frame). The "IFNULL" is in the wrong place as well since you want to set the SUM = 0 if no records are found.


        SELECT products.id, (IFNULL(SUM(incoming_products.measurement), 0) - IFNULL(SUM(outgoing_products.measurement), 0))) as diffFROM products LEFT JOIN incoming_products ON products.id = incoming_products.product_id LEFT JOIN outgoing_products ON products.id = outgoing_products.product_idGROUP BY products.id;


        Troy

        Comment

        Working...
        X