How to sum the values of a column in SQL Server from the current row to the last within a CTE

Question

SQL FIDDLE

I tried to sum the column qx in my CTE from the current row to the last row creating a calculated column called qxsum like the following:

declare @idade int = 25
declare @sexo char = 'm'

;with cte as (
    select
        @idade as idade,
        @sexo as sexo,
        case when @sexo = 'm' then mor.Masculino else mor.Feminino end as qx,
        cast((sum(1.0) over (order by @idade rows between current row and unbounded following)) as float) as qxsum
     from Mortalidade as mor
     where mor.Idade = @idade

     union all

      select
          cte.idade + 1,
          cte.sexo,
          mor.qx,
          cast((sum(cte.qx) over (order by @idade rows between current row and unbounded following)) as float) as qxsum
      from cte

      outer apply (
          select
              case when cte.sexo = 'm' then mor.Masculino else mor.Feminino end as qx
          from Mortalidade as mor
          where mor.Idade = cte.Idade + 1
      ) mor

      where cte.Idade < 120
) select * from cte option (maxrecursion 0);

For instance, for the age 25 in the SQL Fiddle above the qxsum should be the sum from qx from the age 25 to the age 120.

But SQL Server is returning the same value lagging one row, because I am making mistakes and I don't know which mistake I am making.

Answer 1

You want to order by column idade rather than by the variable @idade (whose value is the same for all rows). Also, to generate the window sum() for all ages that are greater or equal than the one of current row, you can just order the records by descending idade (so there is no need for a rows frame).

I find that is simpler to use the recursive cte to just generate the list of idades, and then bring the table with left join in the main query.

declare @idade int = 25;
declare @sexo char = 'm';

with cte as (
    select @idade idade
    union all select idade + 1 from cte where idade < 120
)
select
    c.idade,
    @sexo sexo,
    case when @sexo = 'm' then m.masculino else m.feminino end as qx,
    sum(case when @sexo = 'm' then m.masculino else m.feminino end) 
        over(order by c.idade desc) qxsum
from cte c
left join mortalidade m on m.idade = c.idade
order by c.idade
option (maxrecursion 0);

Note that you should be storing the genders on different rows rather than in columns (this would avoid the need for conditional logic).

For your sample data, this query yields:

idade | sexo | qx           | qxsum        
----: | :--- | :----------- | :------------
   25 | m    | 0.0019300000 | 28.0897400000
   26 | m    | 0.0019600000 | 28.0878100000
   27 | m    | 0.0019900000 | 28.0858500000
   28 | m    | 0.0020300000 | 28.0838600000
   29 | m    | 0.0020800000 | 28.0818300000
   30 | m    | 0.0021300000 | 28.0797500000
  ............. more rows here ............
  111 | m    | 1.0000000000 | 10.0000000000
  112 | m    | 1.0000000000 | 9.0000000000 
  113 | m    | 1.0000000000 | 8.0000000000 
  114 | m    | 1.0000000000 | 7.0000000000 
  115 | m    | 1.0000000000 | 6.0000000000 
  116 | m    | 1.0000000000 | 5.0000000000 
  117 | m    | 1.0000000000 | 4.0000000000 
  118 | m    | 1.0000000000 | 3.0000000000 
  119 | m    | 1.0000000000 | 2.0000000000 
  120 | m    | 1.0000000000 | 1.0000000000