Given table A:
id | value1 | value2 |
---|---|---|
1 | 0.33 | 0.25 |
2 | 0.4 | 0.7 |
and table B:
date | id | quantity |
---|---|---|
01/08 | 1 | 0.25 |
01/08 | 2 | 0.45 |
02/08 | 1 | 1 |
04/08 | 2 | 0.7 |
I would like to create a table which joins A and B by id and sums all value1 and value2 for all dates like this:
date | value1 | value2 |
---|---|---|
01/08 | 0.25 * 0.33 + 0.4 * 0.45 | 0.25 * 0.25 + 0.45 * 0.7 |
02/08 | 0.33 | 0.25 |
03/08 | 0 | 0 |
04/08 | 0.7 * 0.4 | 0.7 * 0.7 |
How can I achieve this?