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.
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.
No comments:
Post a Comment