Systems, consultation and development 

Facebook E-mail

Cumulative totals with CTE

Following on from this post, here is an alternative that uses a lot less grunt. (Using the same testtable)

;with cte (i,v,running_total) as
{
    select i,v,v from testtable where i = 1   // initialise the first entry

    union all

    select x.i, x.v, cte.running_total + x.v 
         from cte join testtable x on cte.i+1 = x.i
}
// now the cte is defined, lets use it
select * from cte
option (maxrecursion 0)

What you may notice from this is the number of reads required.
If we populate the table with 100,000 records, the original sub-query method performs 15 million reads! However, the CTE method comes back with just 1 Million. Impressive!

Its never that simple however, with only 1000 rows, the CTE performs 10,000 reads, but the subselect only 4000!!

Lets see this in a table

RowsCTESub select
1000100004800
50005014151811
10000100279172351

So you can see, as usual with SQL, the answer to which method is best is… *it depends*

He it is Mr Menons script is bytheway a shortstop life of Yeats, but he is supra family, though it is not direct interested with cultivate sprightliness. cheapest essay writing service In adding, I am true, punctual, and can play quick deadlines I guess that to scrap this vogue, professors should specify fewer essays and greater weighting to examinations.

 
Home Top Tech Tips Cumulative totals with CTE