From each value of a column it is necessary to proportionally withdraw a part of a certain amount. In the column are a bunch of rows with integers

1 200000 2 400000 3 100000

It is necessary to subtract from the total amount of 700,000 235,000.

Did so, but takes away not evenly, not proportional to the value of the column

 UPDATE Таблица SET Сумма = Сумма - 2000;
  • stackoverflow.com/questions/19412/… - E_p
  • The question is absolutely not clear, give a sample of the initial data and what should be obtained at the output. - Mike
  • table: Payment by phone: Amount1, Amount2, type of payment. in the columns sum1 and sum2 (numerical values), type of payment (payment or non-payment) - (text value). We need a script that does the following: fill in the Sum1 column with random sums in a certain range, for example, I drive 2,000,000 and this sum is divided into all lines, and only the wage type (payment) is edited. And Column: Sum1 and Sum2 must have the same value. - Roman
  • absolutely nothing is clear - HELO WORD
  • Column (Amount) 100,000 200,000 300,000 500,000 800,000 There are a lot of rows in a column. Necessary: ​​there is a value that is non-constant, I enter into the script this value is valid 300,000 and this value is subtracted from the entire column. should get something like 300,000 / number of lines (5 in this case) and subtracted from each line. 100,000 - 60000 = 40000 200,000 - 60000 = 140,000 300,000 - 60000 = 240,000 500,000 - 60000 = 440,000 800,000 - 60000 = 740000 but the string value must be greater than 0. - Roman

1 answer 1

To begin, calculate the weight contribution of each of the records in the total amount across the table.

 SELECT price, price / ( SELECT SUM(price) FROM balans) AS weight FROM balans

It is the weight that must be multiplied by the subtracted number 235 000 so that it is evenly distributed between all the records in the table.

 SELECT price, 235000 * (price / ( SELECT SUM(price) FROM balans)) AS diff FROM balans

Now it only remains to subtract this value in the UPDATE . However, a normal UPDATE request will not work.

 UPDATE balans SET price = price - 235000 * (price / ( SELECT SUM(price) FROM balans));

It is not allowed to specify the table 'balans' in the list of FROM tables to make changes to it

We'll have to make a multi-query. First, we create a multi-table query with the amount and value that you need to subtract from this amount.

 SELECT balans.price AS original, 235000 * bcount.price / total.summ AS result FROM balans JOIN balans AS bcount ON balans.id = bcount.id JOIN (SELECT SUM(price) AS summ FROM balans) AS total

Now it’s not hard to make an UPDATE request.

 UPDATE balans JOIN balans AS bcount ON balans.id = bcount.id JOIN (SELECT SUM(price) AS summ FROM balans) AS total SET balans.price = bcount.price - 235000 * bcount.price / total.summ

Now, if you count the sum of the columns, instead of 700,000, you get 465,000, just minus 235,000

 SELECT SUM(price) FROM balans