MySQL query using Update, Sum, Where, and Group By

Question

I have a table below named deposit

dep_id	deposit_amount	comp_id
1	100	1
2	100	1
3	300	2
4	200	2
5	100	2
6	500	3

When I update the table with the query below I get the following table, which is not what I want

UPDATE deposit 
SET deposit_amount = (SELECT SUM(deposit_amount) - 50) 
WHERE comp_id =1

What the query above does is to subtract 50 from each of the corresponding comp_id

dep_id	deposit_amount	comp_id
1	50	1
2	50	1
3	300	2
4	200	2
5	100	2
6	509	3

But the table below is what I need. Because seeing the first table and with the query I provided where comp_id =1, we have 100 + 100 = 200, and then 200 - 50 = 150. So because comp_id has 1 IDs two times, therefore we have 75 and 75 because 75 +75 is 150. So we have the table below, which is what I need.

dep_id	deposit_amount	comp_id
1	75	1
2	75	1
3	300	2
4	200	2
5	100	2
6	500	3

The amount supposed to be evenly split amongst the deposits that share a comp_id, even if they weren't before.

Please how do I write the query to suit the table I need? Help!

Answer 1

You can divide the 50 by the count of the records for this id:

UPDATE deposit d1,
  (SELECT *, count(*) over (partition by d2.comp_id) as c FROM deposit d2) x
SET d1.deposit_amount = (SELECT SUM(d1.deposit_amount) - 50/x.c) 
WHERE d1.comp_id =1

see: DBFIDDLE