[Solved] need sum of value based on category [closed]

by


What you need is calculating rolling total.
The fastest way in SQL Server 2005 / 2008 / 2008 R2 I know is described here: https://stackoverflow.com/a/13744550/1744834. You have to have unique secuential column inside each category to use this method, like this:

create table #t (no int, category int, value int, id int, primary key (category, id))

insert into #t (no, category, value, id)
select no, category, value, row_number() over (partition by category order by no)
from test

;with 
CTE_RunningTotal
as
(
    select T.no, T.category, T.value, cast(T.value as decimal(29, 10)) as total_value, T.id
    from #t as T
    where T.id = 1
    union all
    select T.no, T.category, T.value, cast(T.value + C.total_value as decimal(29, 10)) as total_value, T.id
    from CTE_RunningTotal as C
        inner join #t as T on T.id = C.id + 1 and T.category = C.category
)
select C.no, C.category, C.value, C.value
from CTE_RunningTotal as C
option (maxrecursion 0)

SQL FIDDLE EXAMPLE

you could also use shorter query, but performance is worse (I think it’s O(N^2) vs O(N) for recursive CTE):

select t1.no, t1.category, t1.value, sum(t2.value)
from test as t1
    inner join test as t2 on t2.category = t1.category and t2.no <= t1.no
group by t1.no, t1.category, t1.value
order by t1.no

SQL FIDDLE EXAMPLE

6

solved need sum of value based on category [closed]