I have a table let’s assume X and I want to join it to another table Y
this the content of X
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?
Advertisement
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