how to divide the amount for each record on specific condition

I have a table let’s assume X and I want to join it to another table Y

this the content of X

ID    |  value 
------------------
100   | -500
200   | 45
300   | -100

table Y

ID   |  store_code
---------------------
100  | 7001
100  | 7002
100  | 7003
200  | 3001
200  | 3002
300  | 5001

If I made a relationship between X & Y tables the amount it will be duplicated, so if I used sum function the total will be wrong

I decided to divide the amount between the stores for each ID for example the amount for ID 200 will be like this

ID | store_code | amount
200 | 3001 | 22.5
200 | 3002 | 22.5

when I use sum function the result will return to its original value 45

how can I do this calculation in SQL code?

Answer

Hmmm . . . If I understand correctly, you want to allocate the full value over multiple rows. Use window functions:

select y.id, y.store_code,
       (x.value / count(*) over (partition by y.id)) as amount
from x join
     y
     on x.id = y.id