Allocate value to a constant column with a group of multiple rows

Question

I have data in a table like this:

columnA here is constant value within the group of rows with columnB consisting of id's 1,2,3,4

I am trying to write a SQL query to allocate the value of the columnC until complete columnA is allocated in the order of rownum of columnB. For example columnD is the value I am allocating from ColumnC.

So the output should look like this:

I could hardly get any ideas about how to do it.

Answer 1

You can use the SUM analytic function to generate a cumulative total and subtract that from the maximum to see how much capacity you have remaining and then use GREATEST and LEAST to restrict the value to be between 0 and the c column value:

SELECT a, b, c,
       LEAST(
         c,
         GREATEST(
           a + c - SUM(c) OVER (PARTITION BY a ORDER BY b),
           0
         )
       ) AS d
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (a, b, c) AS
SELECT 6000,  1, 3000 FROM DUAL UNION ALL
SELECT 6000,  2, 2000 FROM DUAL UNION ALL
SELECT 6000,  3, 4000 FROM DUAL UNION ALL
SELECT 6000,  4, 2500 FROM DUAL UNION ALL
SELECT 9000,  5, 3000 FROM DUAL UNION ALL
SELECT 9000,  9, 2000 FROM DUAL UNION ALL
SELECT 9000, 15, 3000 FROM DUAL UNION ALL
SELECT 9000, 17, 2500 FROM DUAL;

Outputs:

A	B	C	D
6000	1	3000	3000
6000	2	2000	2000
6000	3	4000	1000
6000	4	2500	0
9000	5	3000	3000
9000	9	2000	2000
9000	15	3000	3000
9000	17	2500	1000

fiddle

Answer 2

Using a running sum with a case expression. Pretty much the same approach as MTO's answer but, arguably, a bit more readable...

SELECT   col_a, col_b, col_c,
         Case When Sum(col_c) Over(Partition By col_a Order By col_b ) <= col_a
              Then col_c
          -- else greatest of (col_a - previous total) and zero
         Else Greatest(col_a - (Sum(col_c) Over(Partition By col_a Order By col_b ) - col_c), 0)
         End as col_d
FROM     tbl 
ORDER BY col_a, col_b

COL_A	COL_B	COL_C	COL_D
6000	1	3000	3000
6000	2	2000	2000
6000	3	4000	1000
6000	4	2500	0
9000	5	3000	3000
9000	9	2000	2000
9000	15	3000	3000
9000	17	2500	1000

fiddle

Answer 3

You can calculate cumulative sum for "columnC", including current row.
Case when "ColumnA" (grand total) is greater than cumulative sum, take full ColumnC for ColumnD.
else take rest of total or 0.

select COLUMNA, COLUMNB, COLUMNC
  ,case when (columnA-cumSumC)>0 then ColumnC
      when (cumSumC-columnC)<ColumnA then ColumnA-(cumSumC-columnC)
   else 0 
   end ColumnD
  ,(columnA-(cumSumC-ColumnC)) rest
  ,cumSumC
from(
  select COLUMNA, COLUMNB, COLUMNC 
    ,sum(columnC)over(partition by columnA order by columnB) cumSumC
  from test t
)a;

COLUMNA	COLUMNB	COLUMNC	COLUMND	REST	CUMSUMC
6000	1	3000	3000	6000	3000
6000	2	2000	2000	3000	5000
6000	3	4000	1000	1000	9000
6000	4	2500	0	-3000	11500
9000	5	3000	3000	9000	3000
9000	9	2000	2000	6000	5000
9000	15	3000	3000	4000	8000
9000	17	2500	1000	1000	10500

fiddle