Search code examples
oracle19c

Using Group By in Update Query

I am trying to calculate weight using update. In the specific case I need to apply grouping as the burden needs to be distributed by cliend id. But the grouping is not working correctly here. It is mandatory to use update

Results

enter image description here

Expected result: the expected result is to have allocated by weight based on client id and holdings 0.14/0.14/1/0.71

    CREATE TABLE VALUE_TBL 
     ( CLIEND_ID NUMBER(10),
       VALUE NUMBER,
       WEIGHT NUMBER,
       HOLDINGS VARCHAR(50)
     );

insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,10,33.33,'GOOGLE');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,10,33.33,'AAPL');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(2,30,100,'ABB');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,50,33.33,'TESLA');

I tried the following update but no success

  update value_tbl y
  set weight = value / 
  (select sum(value) 
  from value_tbl x 
  group by x.cliend_id)
Solution

  • Your subquery will return one row for every ID, not just the total for the ID you are updating. As a correlated update that would look like:

    update value_tbl y
  set weight = y.value / 
  (select sum(x.value) 
  from value_tbl x 
  where x.cliend_id = y.cliend_id)
    CLIEND_ID VALUE WEIGHT HOLDINGS
    1 10 .1428571428571428571428571428571428571429 GOOGLE
    1 10 .1428571428571428571428571428571428571429 AAPL
    2 30 1 ABB
    1 50 .7142857142857142857142857142857142857143 TESLA

    Which is not the result you showed. To get those percentages you would use the count, and divide into 100, not value:

    update value_tbl y
  set weight = 100 / 
  (select count(*) 
  from value_tbl x 
  where x.cliend_id = y.cliend_id)
    CLIEND_ID VALUE WEIGHT HOLDINGS
    1 10 33.33333333333333333333333333333333333333 GOOGLE
    1 10 33.33333333333333333333333333333333333333 AAPL
    2 30 100 ABB
    1 50 33.33333333333333333333333333333333333333 TESLA

    fiddle

    You can use the round, floor/ceil or trunc functions to limit the results to two decimal places, or change your column definition to only store two decimal places.