topic Re: Why does sum functionality on table join return incorrect values in Application Development and Automation Discussions

Why does sum functionality on table join return incorrect values

nidak2501 — Fri, 18 Sep 2020 19:40:33 GMT

Hi All,

I am using 3 tables Table a ,table b and table c.

On using sum on fields from table b and c i am getting incorrect values on both fields and both seem to be greater than correct values.

On joining these tables using a query as follows

Select from Table A left join Table B on a~field = b~field
                                Left join Table on a~field = c~field
Fields
a~field1,a~field2 ,sum( b~field1 ) as qty1,sum( c~field1 ) as qty2


into  internal_Table where .......

Now if i leave out table c then values in sum for table b are correct ,but i need to sum both table fields on join with table a.

Am i missing something ?

Thanking you

Re: Why does sum functionality on table join return incorrect values

chau1995 — Sat, 19 Sep 2020 03:01:18 GMT

Hi,

You can split it into two select statements, one for group table A and B, and one for A and C, after that, join two groups and you will see data.
When you group A and B ==> it will return dataset X
SQL will trigger continuously X with C ==> data will be incorrect
Hope this helps

Re: Why does sum functionality on table join return incorrect values

nidak2501 — Sat, 19 Sep 2020 06:04:44 GMT

Hi Chau,

I understand that splitting them both would give proper result, but my plan requires joining 5 more table with table a in similar manner for

getting sum( ) values ,this would mean splitting them all individually.

Isnt there some methodology for sum and joins to work accordingly?

Regards,

Re: Why does sum functionality on table join return incorrect values

Sandra_Rossi — Sat, 19 Sep 2020 06:46:19 GMT

The first fact you are missing is that when you have table C in your query, the sum of B column is altered because the number of B rows is multiplied by the number of C rows for each A row.

Workaround -> See Chau answer.

Re: Why does sum functionality on table join return incorrect values

chau1995 — Sat, 19 Sep 2020 11:11:44 GMT

You can do it by using window function in SQL. Maybe you can refer it in this link: https://blogs.sap.com/2020/09/07/another-ways-to-calculate-accumulate-and-total-in-amdp/
Hope this helps!!