# After joining tables SUM() function returns wrong value

**URL:** <https://forum.inductiveautomation.com/t/after-joining-tables-sum-function-returns-wrong-value/43618>\
**Category:** General Discussion\
**Tags:** database\
**Created:** [February 19, 2021, 2:28pm UTC](https://forum.inductiveautomation.com/t/after-joining-tables-sum-function-returns-wrong-value/43618 "2021-02-19T14:28:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rwalters1](https://avatars.discourse-cdn.com/v4/letter/r/b9e5f3/32.png) [@rwalters1](https://forum.inductiveautomation.com/u/rwalters1)\
**Post date:** [February 19, 2021, 2:28pm UTC](https://forum.inductiveautomation.com/t/after-joining-tables-sum-function-returns-wrong-value/43618/1 "2021-02-19T14:28:38Z")

</div>

I have two tables (postgres database)-  
**table1** has three columns (_part\_number_, _scrap\_code_, _scrap\_lbs_)  
**table2** has three columns (_fk\_part\_number_, _fk\_scrap\_code_, _operation\_code)_

When I SUM() the _scrap\_lbs_ column from **table1** , without joining **table2** ; I get the expected values.  
But, I need to have the _operation\_code_ in the resulting query.  
When I JOIN **table2** , it appears to multiple the sum of the _scrap\_lbs_ by the number of rows with the _operation\_code_. (So, I know where the larger value is being derived).

Here is my code:

**SELECT** part\_number, scrap\_code, **SUM** (scrap\_lbs) **AS** Total, operation\_code  
**FROM** table1

**JOIN** table2  
**ON** table1.part\_number = table2.part\_number

**WHERE** operation\_code = ( **SELECT** operation\_code **FROM** table2 **WHERE**  
fk\_scrap\_code = scrap\_code **AND** fk\_part\_number = part\_number **GROUP BY** operation\_code)

**GROUP BY** date\_slot, part\_number, scrap\_code, operation\_code;

Thank you- Robert-

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 19, 2021, 2:54pm UTC](https://forum.inductiveautomation.com/t/after-joining-tables-sum-function-returns-wrong-value/43618/2 "2021-02-19T14:54:30Z")

</div>

I'm not 100% sure on this but I think your problem is here

> [@rwalters1](#):
>
> **WHERE** operation\_code = ( **SELECT** operation\_code **FROM** table2 **WHERE**  
> fk\_scrap\_code = scrap\_code **AND** fk\_part\_number = part\_number **GROUP BY** operation\_code)

I think you should try something more like

```auto
JOIN table2
ON table1.part_number = table2.part_number and 
   fk_scrap_code = scrap_code and
   fk_part_number = part_number

```

---

<div class="post-metadata">

**Author:** ![rwalters1](https://avatars.discourse-cdn.com/v4/letter/r/b9e5f3/32.png) [@rwalters1](https://forum.inductiveautomation.com/u/rwalters1)\
**Post date:** [February 19, 2021, 3:06pm UTC](https://forum.inductiveautomation.com/t/after-joining-tables-sum-function-returns-wrong-value/43618/3 "2021-02-19T15:06:49Z")

</div>

You can now be 100% sure-  
Your solution worked as expected-

Thank you for your time; it is very much appreciated-  
Robert-