About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, December 15, 2011

TSQL Example: Cumulative

A quick example on implementing cumaltive logic in a tsql query:

WITH CTE(Name, Observation, RowNum)
AS
(
SELECT
Name,
count(*) as Observation,
ROW_NUMBER() OVER (ORDER BY count(*)desc) as rownum
FROM
dbo.TABLE_LOGIC_STUFF
GROUP BY Name
)

select
c1.Name,
c1.Observation,
(select sum(c2.Observation) from cte as c2 where c2.RowNum <= c1.RowNum) as cumulative
from cte as c1



by adding row number in the cte table, I can then use a subquery to sum all the values below the current rownum.