Why does sum functionality on table join return incorrect values

[nidak2501]

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

[chau1995]

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

[nidak2501]

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,

[Sandra_Rossi]

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.

[chau1995]

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!!