MySQL query using Update, Set, 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

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

• user234725: Is the amount supposed to be evenly split amongst the deposits that share a comp_id? Even if they weren't before?
• Rick James: Where did the 50 come from? Does the 50 not apply to comp_ids 2 and 3?
• Omnigospel: @dwhitemv yes the amount got to be split evenly on the corresponding comp_id
• Omnigospel: @RickJames the 50 only applies to the corresponding comp_id, in this case is 1 according to the query. If comp_id is 2 then amounts that fall under 2 (comp_id) would be affected

Answer 1

See if this is what you want:

UPDATE deposit AS d1
 JOIN ( SELECT 100 / COUNT(*) AS each FROM deposit As d2
 WHERE d2.comp_id = d1.comp_id 
 ) AS x
 SET d1.deposit_amount = d1.deposit_amount - x.each
 WHERE d1.comp_id = 1;

If there are 3 rows instead of 2, then 100/3 = 33.33333... -- This will lead to a roundoff error somewhere. What do you plan to do with the extra or lost cent? I will not be easy to write SQL to give the rows 33,33,34. For this, I recommend application code, not SQL.