Hello! Given a table with information on securities for sale with the name of the paper, the quantity and price. An example of table values ​​for a single Cisco security:

name call_volume price 1- Cisco 300 27 2- Cisco 150 26 3- Cisco 50 26

There is such a request:

 select o.o_stock as name, sum(o.o_amount) as call_volume, o.o_limit as price from orders o, orders calls where o.o_stock = 'Cisco' and o.o_stock = calls.o_stock and o.o_limit <= calls.o_limit and calls.o_type = 'CALL' and o.O_ID = calls.O_ID group by o.o_stock, o.o_limit order by o.o_limit DESC

Query result:

  name call_volume price 1- Cisco 300 27 2- Cisco 200 26

But you need to get:

  name call_volume price 1- Cisco 300 27 2- Cisco 500 26

those. we have a maximum price (27), then volume = the sum of all securities for this max. price (27) further the price is less than max. price (26), then volume = the sum of all securities at this price plus all securities for the most price (in this case, 27) if we had one more line, say:

  name call_volume price 1- Cisco 300 27 2- Cisco 150 26 3- Cisco 50 26 4- Cisco 100 25

then the desired result would be:

  name call_volume price 1- Cisco 300 27 2- Cisco 500 26 3- Cisco 600 25

The question is ... HOW ?? :)) Thank you in advance!

  • sheyay !!!! exactly what is needed!!! thank you very much!!! Everything works now as it should !!! - ElinHaz

2 answers 2

This is called cumulative results .

    Here is the code for your problem:

    Code project: Calculating simple running totals in SQL Server