SQL Server: cumulative percentage with "group by" on many fields

I have this table, and I want to calculate the cumulative percentage for each TYPE_MATERIAL/YEARS

declare @mytable table (TYPE_MATERIAL int, YEARS int, ROW_NUM int, PERCENTUAL_PRICE numeric(6,2))
insert @mytable
select 1,2010,1,54.5
union all select 1,2010,2,37.5
union all select 1,2010,3,8.0

union all select 1,2009,1,72.8
union all select 1,2009,2,21.0
union all select 1,2009,3,6.2

union all select 2,2010,1,61.0
union all select 2,2010,2,36.0
union all select 2,2010,3,3.0

The result should be something like this:

TYPE_MATERIAL YEARS       ROW_NUM     PERCENTUAL_PRICE    PERCENTUAL_PRICE_cumulative
1             2010        1           54.50               54.5 (=54.5)
1             2010        2           37.50               92.0 (=54.5+37.5)
1             2010        3           8.00                100.0 (=54.5+37.5+8)
1             2009        1           72.80               72.8
1             2009        2           21.00               93.8
1             2009        3           6.20                100.0
2             2010        1           61.00               61.0
2    开发者_Go百科         2010        2           36.00               97.0
2             2010        3           3.00                100.0

I found this query on internet, but it's not good for this case because it calculate the cumulative percentage only if I have one TYPE_MATERIAL and one YEARS

select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM
)
from @mytable a

Any suggestions? THANKS

select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.YEARS = a.YEARS AND b.TYPE_MATERIAL = a.TYPE_MATERIAL
)
from @mytable a

Your problem is caused by your inner query not restricting on type_material and years.

select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.type_material = a.type_material AND b.years = a.years
)
from @mytable a

You could also do it with a recursive CTE which should perform ok for very large data.

with myCte (TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE_cumulative)
as
(
    select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE AS PERCENTUAL_PRICE_cumulative
    FROM @mytable
    WHERE row_num = 1
    UNION ALL
    select t.TYPE_MATERIAL, t.YEARS, t.ROW_NUM, t.PERCENTUAL_PRICE, CAST(t.PERCENTUAL_PRICE + myCte.PERCENTUAL_PRICE_cumulative AS  numeric(6,2)) AS PERCENTUAL_PRICE_cumulative
    FROM @mytable t
        INNER JOIN myCte ON myCte.type_material = t.type_material AND myCte.years = t.years AND mycte.row_num = t.row_num-1
    WHERE t.row_num > 1
)
SELECT * FROM myCte
ORDER BY  TYPE_MATERIAL, YEARS, ROW_NUM

This should be solved with window functions, not recursion or subqueries:

SELECT
  type_material, years, row_num, percentual_price
  SUM (percentual_price) OVER (PARTITION BY type_material, years ORDER BY row_num) cumulative
FROM @mytable