Skip to content
Advertisement

Cumulative sum of a column

I have a table that has the below data.

COUNTRY LEVEL   NUM_OF_DUPLICATES
  US    9           6
  US    8           24
  US    7           12
  US    6           20
  US    5           39
  US    4           81
  US    3           80
  US    2           430
  US    1           178
  US    0           430

I wrote a query that will calculate the sum of cumulative rows and got the below output .

COUNTRY LEVEL   NUM_OF_DUPLICATES      POOL
  US    9           6                   6
  US    8           24                  30
  US    7           12                  42
  US    6           20                  62
  US    5           39                  101
  US    4           81                  182
  US    3           80                  262
  US    2           130                 392
  US    1           178                 570
  US    0           254                 824

Now I want to to filter the data and take only where the POOL <=300, if the POOL field does not have the value 300 then I should take the first value after 300. So, in the above example we do not have the value 300 in the field POOL, so we take the next immediate value after 300 which is 392. So I need a query so that I can pull the records POOL <= 392(as per the example above) which will yield me the output as

COUNTRY LEVEL   NUM_OF_DUPLICATES      POOL
  US    9           6                   6
  US    8           24                  30
  US    7           12                  42
  US    6           20                  62
  US    5           39                  101
  US    4           81                  182
  US    3           80                  262
  US    2           130                 392

Please let me know your thoughts. Thanks in advance.

Advertisement

Answer

declare @t table(Country varchar(5), Level int, Num_of_Duplicates int)
insert into @t(Country, Level, Num_of_Duplicates)
values
('US', 9, 6),
('US', 8, 24),
('US', 7, 12),
('US', 6, 20),
('US', 5, 39),
('US', 4, 81),
('US', 3, 80),
('US', 2, 130/*-92*/),
('US', 1, 178),
('US', 0, 430);


select *, sum(Num_of_Duplicates) over(partition by country order by Level desc),
(sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates) / 300 as flag,--any row which starts before 300 will have flag=0
--or
case when sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates < 300 then 1 else 0 end as startsbefore300
from @t;

select *
from
   (
    select *, sum(Num_of_Duplicates) over(partition by country order by Level desc) as Pool
    from @t
) as t
where Pool - Num_of_Duplicates < 300 ;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement