# a problem with calculation

**URL:** <https://forums.percona.com/t/a-problem-with-calculation/1438>\
**Category:** Other MySQL® Questions\
**Created:** [June 25, 2010, 3:57am UTC](https://forums.percona.com/t/a-problem-with-calculation/1438 "2010-06-25T03:57:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![shahaneh](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@shahaneh](https://forums.percona.com/u/shahaneh)\
**Post date:** [June 25, 2010, 3:57am UTC](https://forums.percona.com/t/a-problem-with-calculation/1438/1 "2010-06-25T03:57:00Z")

</div>

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.product\_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.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [June 25, 2010, 5:07pm UTC](https://forums.percona.com/t/a-problem-with-calculation/1438/2 "2010-06-25T17:07:11Z")

</div>

-

---

<div class="post-metadata">

**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\
**Post date:** [June 25, 2010, 8:21pm UTC](https://forums.percona.com/t/a-problem-with-calculation/1438/3 "2010-06-25T20:21:03Z")

</div>

| [B]shahaneh wrote on Fri, 25 June 2010 05:27[/B] |
| 

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.product\_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.product\_id)GROUP by incoming\_products.product\_id;

---

<div class="post-metadata">

**Author:** ![Troy](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Troy](https://forums.percona.com/u/Troy)\
**Post date:** [June 29, 2010, 1:52pm UTC](https://forums.percona.com/t/a-problem-with-calculation/1438/4 "2010-06-29T13:52:25Z")

</div>

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