How do I implement this?

I need the sum of all the values ​​from the PPLAN column, and from this sum I need to subtract the PPLAN value.

Assume the entire amount = 89 473,79 .
The first line will be the value 89473,79 - 2182,29 = 87291.5 ,
the second will be 89473,79 - 2182,29 - 2182,29 = 85109.21
etc., until it is already at the end of 0 .

The number of lines is always different, because it usually depends on the period of months, maybe 20 or 40, for example. Therefore, I decided to build on the entire amount.

How can I put a counter? Or is the logic of solving the problem itself incorrect?

enter image description here

  • And what does the first line mean, the second ... You after all know that the order of the lines is not guaranteed? - Andrey NOP
  • Do you really have MySQL? as the picture is very much on pl / sql developer looks like, and this is an oracle and the solution in it is absolutely not the same as in MySQL ... And yes, you need to specify in what order the records are sorted to determine which one is first, second, etc. - Mike
  • in mysql 8, the solution with window functions should be quite simple - teran
  • @teran and in earlier versions it is easy to give the previous value of a variable before adding a pplan to it when sorting in the reverse order. only I sooo strongly doubt that the question is really in MySQL, and the author of the question asked the question and left. Well, in oracle, yes, there will be a window function - Mike
  • Yes, I do not want to get the amount in the subquery, and the whole short ... deduct. In your case, deduct the amount with accumulation. But much faster in the subquery to get, on the contrary, the amount of this field with the accumulation, but when you reverse the sort, and in an external query, just reverse the sort. - Akina

2 answers 2

I would suggest this:

 select sum(pplan) over () - sum(pplan) over (order by duedate) from mytable

The first sum(pplan) over () is just the full amount, and the sum(pplan) over (order by duedate) is the amount accumulated, respectively, you will have the values 89473,79 , 89473,79 - 2182,29 in each next line 89473,79 - 2182,29 , 89473,79 - 2182,29 - 2182,29 and so on.

  • But can this be written through to_char? without select - zharzhanov.miras
  • @ zharzhanov.miras I mean? Do you want to format the result? Would have tried for a long time already. Of course you can, the whole expression is stuffed into to_char , it is converted to a string. - Dmitriy

In fact, you need the sum of all subsequent lines relative to the current one in the specified sorting. So you can not deduct anything and get the amount directly over the required range of lines:

 select T.*, nvl( sum(pplan) over(order by duedate rows between 1 following and unbounded following) ,0) from Table T

The last line returns NULL. The next 1 line is gone, turning it to 0 with nvl.

Information on the operation of the over () clause can be found in the Oracle analytic function documentation.

  • Can I write your query as: to_char (sum (pplan) over () - sum (pplan) over (order by / * duedate * / tt.rs_dd)) as t_pplan to select? - zharzhanov.miras
  • @ zharzhanov.miras You can of course, and even my version, you put the entire nvl inside. It is certainly longer, but somehow I like it more than the calculation of two sums and finding the difference, the work of the database is less. - Mike