I've been trying to do the following:
CREATE TABLE T_example
(category number(1,0),
amount number(4,0),
amount2 number(4,0))
INSERT INTO T_example VALUES (1,20,40);
INSERT INTO T_example VALUES (1,30,40);
INSERT INTO T_example VALUES (2,5,60);
INSERT INTO T_example VALUES (2,15,60);
INSERT INTO T_example VALUES (2,30,60);
As you can see all rows contain the same amount2 within their category. Now i want to spread amount2 within each category according to the distribution of amount with the category.
UPDATE T_example
SET amount2 = amount2 * amount / SUM(amount) OVER (PARTITION BY category ORDER BY category);
I want to get:
category - amount - amount2
1 - 20 - 16
1 - 30 - 24
2 - 5 - 6
2 - 15 - 18
2 - 30 - 36
But the code doesn't work. It says:
00934.00000 - "group function is not allowed here"
Can you tell me where I am mistaken?
I think below will work for you,co-related sub-query
UPDATE T_example t1
SET t1.amount2 = (t1.amount*t1.amount2) / (
select sum(amount) from -- from was missing
T_example t2 where t2.category=t1.category
group by category
);
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e2c00fe7ad8866bb4a62f66b08133f95
CATEGORY AMOUNT AMOUNT2
1 20 16
1 30 24
2 5 6
2 15 18
2 30 36